I do not know SQL but learning fast and furious.

I am programming an agent and working with a group of existing databases.
I would like to able to compare the database before and after an update.
The testing databases are relatively small.
I have no problem programming some compare but how do I go about it.

Should I do this in SQL duplicating the database.
I would be happy to write some SQL and dump the databases and do the compare
externally.

I would appreciate any suggestion.

Andre

If you just want to compare data between similar tables you can do so
with a JOIN:

SELECT COALESCE(A.key_col, B.key_col),
COALESCE(A.col1, B.col1), COALESCE(A.col2, B.col2), ...
FROM TableA AS A
FULL JOIN TableB AS B
ON A.key_col = B.key_col
WHERE COALESCE(A.col1,'')<>COALESCE(A.col1,'')
AND COALESCE(A.col2,'')<>COALESCE(A.col2,'')

assuming key_col is the primary key in both tables.

--
David Portas
SQL Server MVP
--

What I would like to do is probably
1) back up the data base
2) restore it under a different name
--- run my agent
3) create a difference database ( a new database with any table which is
different)

Step 1 and 2 are easy so can be ignored
now step 3
I can create a new temporary database but how can I fill the tables in this
database using SQL



"David Portas" <REMOVE_BEFORE_REPLYING_dportas@acm.org> wrote in message
news:1111586542.235632.69940@o13g2000cwo.googlegro ups.com...
> If you just want to compare data between similar tables you can do so
> with a JOIN:
>
> SELECT COALESCE(A.key_col, B.key_col),
> COALESCE(A.col1, B.col1), COALESCE(A.col2, B.col2), ...
> FROM TableA AS A
> FULL JOIN TableB AS B
> ON A.key_col = B.key_col
> WHERE COALESCE(A.col1,'')<>COALESCE(A.col1,'')
> AND COALESCE(A.col2,'')<>COALESCE(A.col2,'')
>
> assuming key_col is the primary key in both tables.
>
> --
> David Portas
> SQL Server MVP
> --
>

Andre Arpin (arpin@kingston.net) writes:
> What I would like to do is probably
> 1) back up the data base
> 2) restore it under a different name
> --- run my agent
> 3) create a difference database ( a new database with any table which is
> different)
>
> Step 1 and 2 are easy so can be ignored
> now step 3
> I can create a new temporary database but how can I fill the tables in
> this database using SQL

Red Gate has products for this, check out http://www.red-gate.com/.

If you would like to roll your own, you would have to write a query
like the one that David showed you for each table.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

You can easily populate a table from another in a different database:

INSERT INTO DatabaseA.dbo.TableA (col1, col2, ...)
SELECT col1, col2, ...
FROM DatabaseB.dbo.TableB
WHERE ... ?

--
David Portas
SQL Server MVP
--