I’m currently working on a project where we are importing data transactions on a daily basis and need to compare the imported file with the current file. One of my favorite SQL clauses is the NOT IN clause, which I have used many times to do comparison type queries. Quite often, this powerful clause will help to simplify a system so much that its almost unbelievable.
This is how it looks;
WHERE dbo.revenueMaster_import.rowKey NOT IN(
Â SELECT DISTINCT
Â Â dbo.revenueMaster_archive.rowKey
Â Â dbo.revenueMaster_archive)
ORDER BY dbo.revenueMaster_import.seqno asc
This query shows all of the distinct rows from dbo.revenueMaster_import that are NOT IN dbo.revenueMaster_archive. There are many ways you might need to compare data like this. I’ve created many systems that utilize this technique to help keep a systems table structure to a minimum and therefore reduce a systems complexity.
Happy memorial Weekend!