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 mergeKe...