SCD 2 Using Merge statement in SQL server

 In this example we will look at how to implement SCD2 using merge statement.

Target table:

Source Table:

So if you compare the source and target table , you can see that 

* New address for customerid = 1 in the source table , so "New" address needs to be inserted (true) and  "current" address in traget table (customer) needs to be updated as 'false'.

* No changes to the address of customer 3 , so no changes.

* A "new" customer with customerid = 4 , so that needs to be inserted.

Query for SCD 2 :

MERGE INTO customers

USING (

   -- These rows will either UPDATE the current1 addresses of existing customers or INSERT the new addresses of new ----customers

  SELECT updates.customerId as mergeKey, updates.*

  FROM updates

  

  UNION ALL

  

  -- These rows will INSERT new addresses of existing customers 

  -- Setting the mergeKey to NULL forces these rows to NOT MATCH and be INSERTed.

  SELECT NULL as mergeKey, updates.*

  FROM updates JOIN customers

  ON updates.customerid = customers.customerid 

  WHERE customers.current1 = 'true' AND updates.address <> customers.address 

  

) staged_updates

ON customers.customerId = mergeKey

WHEN MATCHED AND customers.current1 = 'true' AND customers.address <> staged_updates.address THEN  

  UPDATE SET current1 = 'false', endDate = staged_updates.effectiveDate    -- Set current1 to 'false' and endDate to source's effective date.

WHEN NOT MATCHED THEN 

  INSERT(customerid, address, current1, effectivedate, enddate)

  VALUES(staged_updates.customerId, staged_updates.address, 'true', staged_updates.effectiveDate, null);

Detailed Analysis of the merge query :

* Step1:

SELECT updates.customerId as mergeKey, updates.* FROM updates

 These rows will either UPDATE the current addresses of existing customers or INSERT the new addresses of new customers

* Step 2:

SELECT NULL as mergeKey, updates.*   FROM updates JOIN customers ON updates.customerid = customers.customerid 

WHERE customers.current1 = 'true' AND updates.address <> customers.address 

These rows will INSERT new addresses of existing customers  and Setting the mergeKey to NULL forces these rows to NOT MATCH and be INSERTed.

* Step 3 :

In step 3 , we are doing merging the output of step 1 and step 2 to get the complete list of insert and updates.

* Step 4 : In this step we are comparing the result from step 3 and target table (customer) to identify the records that needs to be inserted (when no match found) and records that's needs to be updated (when match found and address is different in source and target)

Final Output after running the merge query: 

Hope this helps!