How to compare data between staging and operational tables?

Question

I just want to ask how to compare two database tables, staging vs. operational tables.

After we did the transformation and convert it to correct datatype like convert flag column (Y/N) nvarchar to bit or to int, how to compare data between the two tables?

Answer 1

There are a couple of options if you want to use T-SQL and SSIS.

You could compare the key columns on the staging table vs ops table to determine if the row already exists and this way you would know if you need to do an INSERT or UPDATE.

If using SSIS you can use the lookup component. Your source componenet will have something like

• SELECT keycol FROM OpsTable.

The lookup component will have a source something like

• SELECT keycol FROM StagingTable.

You can use the 'No Match Output' option to get all the rows in OpsTable not in StagingTable.

If using T-SQL you can use the EXCEPT operator like so

SELECT keycol FROM OpsTable
EXCEPT
SELECT keycol FROM StagingTable 

If you need to compare all the columns (key columns and non key columns) you can use the HASHBYTES and hash all the non key columns for comparison. Please note that HASHBYTES only accepts (VARCHAR, NVARCHAR, or VARBINARY) so you will have to convert any columns that are DATE or such into VARCHAR.

SELECT keycol,
       HASHBYTES('SHA1',col1 + col2 + ISNULL(convert(nvarchar(100),ColDate,109),'') 
  FROM OpsTable

Hope this helps.

---

You can get more information on HASHBYTES here and on the EXCEPT operator here.