SQL Server Tips
How to compare two tables
Sample SQL statements to compare data in two tables with identical structure.
(These statements work for MS SQL Server, as well as for many other databases.)
To find records that exist in the source table but not in the target table:
SELECT * FROM t1 WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.Id = t1.Id)
or
SELECT * FROM t1 LEFT OUTER JOIN T2 on t1.Id = t2.Id WHERE t2.Id IS NULL
If the primary key consists of more than one column, you can modify the SQL statement:
SELECT Id, Col1 FROM t1 WHERE NOT EXISTS
(SELECT 1 FROM t2 WHERE t1.Id = t2.Id AND Col1.t1 = Col2.t2)
On SQL Server 2005 or newer you can use the EXCEPT operator:
SELECT Id, Col1 FROM t1 EXCEPT SELECT Id, Col1 FROM t2
To find records that exist in the source table but not in the target table, as well as records that exists in the target table but not in the source table:
SELECT * FROM (SELECT Id, Col1 FROM t1, 'old'
UNION ALL
SELECT Id, Col1 FROM t2, 'new') t
ORDER BY Id
Note: For tables with large amounts of data UNION statement may be very slow.
Testimonials
Here's why our customers love SQL Server Comparison Tool
Joshua Young, Triyon LLC
Several months ago I downloaded the trial versions of several DB Compare utilities that I was able to find using google. I had considerable trouble with most of the them crashing when attempting to analyze a production system, but using your product I was able to isolate and solve the problems I was having. I decided to add it to my toolkit and have recommended it to a few others.
Casey Florig, Certegy
The tables comparison details is great! And the addition of details for stored procedures comparisons is a big plus! I've forwarded my results to management, and they should be purchasing licenses soon. Thanks for your support and I look forward to utilizing this tool.