SCD Type – 2 using MERGE and Output $action

In this article we will talk about achieving SCD behavior using Sql MERGE statement. MERGE join works well based on join condition with source table and through available condition you can have INSERT, UPDATE or DELETE actions. Primarily what it does is that it compare key value of target with source table and perform different operations specified like WHEN MATCHED, WHEN NOT MATCHED etc.

The main focus of this post is to make use of $action virtual output column when we execute MERGE sql and decide as in whether INSERT or UPDATE has been performed on the source.

Let get to the business area and see these in action.

Create a table first and populate some records, or you can use the same table from my previous post.
http://takeatourtobi.blogspot.com/2011/06/delete-and-truncate-behavior.html

--Create student table CREATE TABLE [DBO].[TTB_STUDENT]( ID INT, NAME VARCHAR(100) NULL, AGE INT NULL, ACTIVE CHAR(1) ) --Insert some records INSERT INTO [DBO].[TTB_STUDENT] VALUES (1, 'Mark', 32, 'Y'), (2, 'Elaine', 31, 'Y'), (3, 'Elton', 33, 'Y'), (4, 'John', 40, 'Y'), (5, 'Smith', 30, 'Y')

Select student table. This is our target table -

Create another table which will have the delta which we have to apply on student table. Note that we will be using ID column value as key to decide whether or not student already exists. Here is the delta table script and rows..

CREATE TABLE [DBO].[TTB_STG_STUDENT]( ID INT PRIMARY KEY, NAME VARCHAR(100) NULL, AGE INT NULL ) INSERT INTO [DBO].[TTB_STG_STUDENT] VALUES (1, 'Mark Luther', 42), (2, 'Elaine Wang', 41), (6, 'Elton B', 22)

Since we are doing SCD Type – 2, this is what will be the scenarios -

• If get a new student (New ID value), we will insert into the target table and set Active = ‘Y’
  
• If get an existing student, we will insert it to the target table with Active = ‘Y’ and set Active = ‘N’ for all other records of that student. Meaning, at a any given point in time, we should have only one active record for each student.

As said earlier idea here is to make use of OUTPUT $action to retrieve the records that got updated and once we have this list, we can insert it finally and set flag to ‘N’ for all other records for a student.

Here is the final SQL statement and target table output.

INSERT INTO [DBO].[TTB_STUDENT] SELECT ID, NAME, AGE, 'Y' FROM ( MERGE [DBO].[TTB_STUDENT] AS TRG USING [DBO].[TTB_STG_STUDENT] AS SRC ON (SRC.ID = TRG.ID AND TRG.ACTIVE = 'Y') WHEN NOT MATCHED THEN INSERT VALUES (SRC.ID, SRC.NAME, SRC.AGE, 'Y') WHEN MATCHED THEN UPDATE SET TRG.ACTIVE = 'N' OUTPUT $action, SRC.ID, SRC.NAME, SRC.AGE ) AS DELTA (action, ID, Name, Age) WHERE action = 'Update'

As you see, we had 2 existing students in the delta table for which active is set to ‘N’ and 2 new entries have been created in the table with Active = ‘Y’. There were one new student (ID = 6), which got inserted into the table. This way we can achieve SCD Type – 2 using MERGE SQL. Please leave your comments/feedback.