Compare Two Tables to Find Unmatched Records

In database programming, sometimes you have to compare two tables (or two views of more than two tables) to find the unmatched records and the difference between two records in two tables with the same identity. As an example, in database migration you have a legacy database and new database with two different database schemata. Your task is to migration all data from the legacy database to the new one so how do you validate your result? At this time, you have to use this technique to see your result; what records you missed in the legacy database; what records you migrated with the wrong result.
One of the fastest and easiest ways to do so is using UNION. The idea is we use UNION to union two tables on all columns which we want to compare. Then we group the union on all columns which we want to compare. If the all columns are identical we get COUNT (*) equal 2 otherwise all unmatched records and the records in one table but not in other table will have COUNT (*) equal 1.

SELECT MIN (tbl_name) AS tbl_name, PK, column_list
FROM
 (
  SELECT ' source_table ' as tbl_name, S.PK, S.column_list
  FROM source_table AS S
  UNION ALL
  SELECT 'destination_table' as tbl_name, D.PK, D.column_list
  FROM destination_table AS D 
)  AS alias_table
GROUP BY PK, column_list
HAVING COUNT (*) = 1
ORDER BY PK

In the above query:

• tbl_name is just additional column name to display the name of tables you want to compare. These are source_table and destination_table
• PK is identity name of source_table and destination_table
• column_list is the list of column you want to compare
• The MIN aggregate on table_name is just arbitrary. It is used because we only returns group of rows in which there was no consolidation with GROUP BY.

If two tables are identical on the column list you compared, no row will return.

• « Previous page
• Next page »