How to compare 2 tables based on a column in MS SQL?

Question

Hi guys,

There is a reconciliation between the two systems at certain periods, but this reconciliation is compared with the result obtained by querying the database and excel file. To automate this manual process, I insert the incoming excel into a table in the database with SQL Bulk Copy. I will then compare these two tables.

The GameConfirmResponses table has all the transactions. (It may make more sense to filter by the date range to be reconciled.) RazerReconciliation is the table that I populated from excel. There might be transactions that the GameConfirmResponses table has and the RazerReconciliation table does not and vice versa so somehow I need to check that scenario too in the SQL query.

I hope I was able to explain my problem.

Select ReferenceNo
From RazerReconciliation A
Where NOT EXISTS
( select referenceId from GameConfirmResponses B
Where A.ReferenceNo = B.referenceId
);

Answer 1

How about?

Select ReferenceNo, 'In Razer' as Type From RazerReconciliation A 

Where NOT EXISTS 
( select referenceId from GameConfirmResponses B Where A.ReferenceNo = B.referenceId )

UNION ALL

select referenceId, 'In Game' as Type from GameConfirmResponses a

 where not exists (select 1 from 

RazerReconciliation b WHERE a.ReferenceID = b.ReferenceNo)
;