Sample SQL to compare tables in SQL Server 2005.

We want to compare two tables to find all duplicates, as well as all unique records, in each table. These queries are typically used to verify that nothing was lost or changed, after a database move. There is an old, hard way to write these queries, as well as a new easy way to write them. Let's look at both! Assume two tables, with five records in each one. This is the old table

SELECT * FROM OLD_Table

firstnamelastname
CharltonHeston
ClintEastwood
DickTracy
HumphreyBogart
MickeyMouse


This is the new table

SELECT * FROM NEW_Table

firstnamelastname
CharlieChaplin
OprahWinfrey
DickTracy
DonaldDuck
MickeyMouse


Let's start with finding all duplicates. The newer, easier to read, way of writing this query is this:

SELECT * FROM OLD_Table INTERSECT SELECT * FROM NEW_Table

firstnamelastname
DickTracy
MickeyMouse


The older, harder to read, way of writing this query is this:

SELECT * FROM OLD_Table t1 WHERE EXISTS (SELECT * FROM NEW_Table t2 WHERE t2.firstname = t1.firstname)

firstnamelastname
DickTracy
MickeyMouse


Next, we want to find all the records that are ONLY in the OLD table. New-style form of the query:

SELECT * FROM OLD_Table EXCEPT SELECT * FROM NEW_Table

firstnamelastname
CharltonHeston
ClintEastwood
HumphreyBogart


And this is the old way of writing the same query:

SELECT * FROM OLD_Table t1 WHERE NOT EXISTS (SELECT * FROM NEW_Table t2 WHERE t2.firstname = t1.firstname)

firstnamelastname
CharltonHeston
ClintEastwood
HumphreyBogart


Finally, we want to find all the records that are ONLY in the NEW table. New style query:

SELECT * FROM NEW_Table EXCEPT SELECT * FROM OLD_Table

firstnamelastname
CharlieChaplin
DonaldDuck
OprahWinfrey


...and the old-style form of the same query:

SELECT * FROM NEW_Table t1 WHERE NOT EXISTS (SELECT * FROM OLD_Table t2 WHERE t2.firstname = t1.firstname)

firstnamelastname
CharlieChaplin
OprahWinfrey
DonaldDuck





Valid XHTML 1.0 Transitional Valid CSS!
        

MCP icon
MCTS icon