Im looking for confirmation I think mostly. Im trying to prototype a Merge of data from a Temp staging table to our final table. This data could change a couple of times a day. We do this today in varying ways, 1 we delete the dataset on target completely and re-insert. This is quick, but terrible for replication performance. 2. We use the binary_checksum feature to check for updated records and Update, Insert or delete as necessary. This is great for replication, but terrible performance and Binary_checksum is greatly flawed.
I am vetting out a change to MERGE, which appears to allow me to check each and every column instead of a checksum value. My preliminary tests are very fast as well. However, I have not solidified my ON criteria correctly I think because I get more records with the second import than I should have (which I think means Im getting more inserts than I should have). I know this example is missing the table, etc... Im mostly looking if someone see's what I am missing in general.
MERGE FinalTender With (HOLDLOCK) AS TARGET
Using Select (all columns) from TempTender Group by (all columns)
------ HERE on the ON is where I think I get confused I have tried listing all columns or only a select number, but Im still not getting the right results. Close, but not right.
ON (Target.DOB = Source.DOB
Target.EmpID = Source.EmpID
Target.CheckID = Source.CheckID
Target.StoreID = Source.StoreID
Target.TenderType = Source.TenderType
----- HERE on the matches, I could update all remaining fields, but the fact is, a tendered check could have more than one tender amount, maybe even two Visa payments of different or same amounts payment type, etc, so that ON list above is not enough. The data can be so varied
------- If I list all columns instead of just a few, I think I would need to Delete from Source then because there is nothing to update as all data matches. Sounds logical to me except I dont understand when an UPDATE occurs.
WHEN NOT MATCHED ON TARGET:
------- This must be a new tender record from source, so I want to Insert into Target. No confusion here