Compare 2 SQL tables to find missing rows fix the difference.

Problem

I have 2 database tables in 2 different databases and they should contain the same data. In this particular case the data is inserted by an application that inserts the data row by row. The primary key is of type uniqueidentifier with default value newid(), so it will never be the same in both tables. The table should have the same number of rows and all columns should contain identical data.

Solution

The T-SQL clause EXCEPT can help to verify if the tables contain the same rows.

From the SQL-BOL: EXCEPT returns any distinct values from the left query that are not also found on the right query.

Example

Compare all columns:

SELECT * FROM db1.dbo.Product
EXCEPT
SELECT * FROM db2.dbo.Product

This will return all rows in table of db1 that are not found or different from the table in db2.

But with the primary key column that is always different (because the guid is generated per table and per row) all rows will be returned, even when the number of rows is equal and all the other columns are equal.

To compare the columns that are different except the primary key column we slect the columns by name, except the primary key column:

SELECT [name], [price], [categoryid], [supplierid] FROM db1.dbo.Product
EXCEPT
SELECT [name], [price], [categoryid], [supplierid] FROM db2.dbo.Product

In this case all the rows that are in db1 but not in db2 will be show.

To add the missing rows to the db2 table (the primary key has default value newid()):

INSERT INTO db2.dbo.Product ([name], [price], [categoryid], [supplierid])
SELECT [name], [price], [categoryid], [supplierid] FROM db1.dbo.Product
EXCEPT
SELECT [name], [price], [categoryid], [supplierid] FROM db2.dbo.Product