MSSQL: comparing contents of database tables

Some simple and useful queries that helped me compare data between different tables:

To find records which exist in source table but not in target table:

SELECT * FROM T1 WHERE NOT EXISTS ( SELECT * FROM T2 WHERE T1.KEY = T2.KEY )
or
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.KEY = T2.KEY WHERE T2.KEY IS NULL

If the primary key consists of more than one column, you can modify SQL statement:

SELECT key, col1 FROM T1 WHERE NOT EXISTS
  (SELECT 1 FROM T2 WHERE T1.key = T2.KEY AND T1.col1 = T2.col2)

On SQL Server 2005 or newer you can use the EXCEPT operator:

SELECT key, col1 FROM T1 EXCEPT SELECT key, col1 FROM T2

To find records which exist in source table but not in target table, as well as records which exists in target table but not in source table:

SELECT * FROM 
              (SELECT key, col1 FROM T1, 'old' UNION ALL
               SELECT key, col1 FROM T2, 'new') t3
ORDER BY key

Leave a Reply

*

captcha *