Updating SCD2 with MERGE statement

712 Views Asked by Grigory P At 21 October 2024 at 01:45

I want to update the SCD-2 table using MERGE statement. So I have:

MERGE TARGET as t
USING SOURCE as s 
ON s.KEY = t.KEY

Case 1:

WHEN MATCHED and s.CHECKSUM <> t.CHECKSUM

In this case I want first UPDATE row in TARGET set row_actual_to = getdate() AND second INSERT row from SOURCE into TARGET

Case 2:

WHEN NOT MATCHED

Just INSERT new row

How to do that in one statement?

Listing both UPDATE and INSERT in Case 1 results in error : A MERGE statement must be terminated by a semi-colon (;).

There are 1 best solutions below

MLeblanc On 20 September 2020 at 10:21

Do not use merge statement. Merge statement has a long list of "won't fix" bug that can corrupt your data under certain circumstance.

Using an Update statement then an insert statement is simplier, easier to debug and will perform better then a merge stagement