Compairing updated data base.

[Andre Arpin]

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

[David Portas]

Re: Compairing updated data base.

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]

Re: Compairing updated data base.

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_dporta s@acm.org> wrote in message
news:1111586542 .235632.69940@o 13g2000cwo.goog legroups.com...[color=blue]
> 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
> --
>[/color]

[Erland Sommarskog]

Re: Compairing updated data base.

Andre Arpin (arpin@kingston .net) writes:[color=blue]
> 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[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: Compairing updated data base.

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

INSERT INTO DatabaseA.dbo.T ableA (col1, col2, ...)
SELECT col1, col2, ...
FROM DatabaseB.dbo.T ableB
WHERE ... ?

--
David Portas
SQL Server MVP
--