Best query to compare data from two tables from two database.

[EM1107]

Good day everyone.

I am required to write a query to find out what change in a table when you run an application. To accomplish that I have create a copy of the database and then did some change on the application connected to my primary database.

In order for me to find out what as change I need to be able to compare the two database and find the difference.

Can anyone tell me what would be the best way to create a query that will return the difference between rows in different databases having the same tables and columns.

Thanks in advance

 

[RiverGuy]

The best way is to use a tool like Red Gate's SQL Data Compare.

You could also do something like

SELECT a.*, b.*
FROM (SELECT *, CHECKSUM(*) AS CS FROM FirstTable) a
INNER JOIN (SELECT *, CHECKSUM(*) AS CS FROM SecondTable) b
  ON a.KeyColumn = b.KeyColumn
WHERE a.CS <> b.CS

but that will just tell you which rows changed, not which columns changed.

 

[EM1107]

I tried it but I get the following error.
Msg 8116, Level 16, State 4, Line 1
Argument data type text is invalid for argument 21 of checksum function.