I was going through some notes I had from previous projects and came across a sample script for created a Type 2 Slow Changing Dimension (SCD) in a database or data warehouse.

A Type 2 SCD is one where new records are added, but old ones are marked as archived and then a new row with the change is inserted. This allows for a complete historical trail of the row’s changes in detail. Sometimes this can be overkill, but in some cases it is required. The client was doing medical claims processing, so we were required to keep a full history of key information.

In the past (read: pre-2008 versions of SQL) this used to have to be done with a bunch of little code and triggers to make sure the old data was kept.

On one of my recent projects I decided I wanted to use the new MERGE statement to create this same effect, with a LOT less code to maintain.

What actually made this possible is the MERGE statement’s output option. The merge statement actually captured all the changes it makes and tags them in an output table that you can reference. This feature let me capture information that was in the merge portion and pass it through to another statement.

Ok, enough blather, here’s the code sample:

INSERT  INTO Customer_Master
        SELECT  Source_Cust_ID ,
                First_Name ,
                Last_Name ,
                Eff_Date ,
                End_Date ,
                Current_Flag
        FROM    ( MERGE Customer_Master CM
                    USING Customer_Source CS
                    ON ( CM.Source_Cust_ID = CS.Source_Cust_ID )
                    WHEN NOT MATCHED 
                        THEN
INSERT           VALUES
                ( CS.Source_Cust_ID ,
                  CS.First_Name ,
                  CS.Last_Name ,
                  CONVERT(CHAR(10), GETDATE() - 1, 101) ,
                  '12/31/2199' ,
                  'y'
                )
                    WHEN MATCHED AND CM.Current_Flag = 'y'
                        AND ( CM.Last_Name <> CS.Last_Name )
                        THEN
 UPDATE          SET
                CM.Current_Flag = 'n' ,
                CM.End_date = CONVERT(CHAR(10), GETDATE() - 2, 101)
                    OUTPUT
                        $Action Action_Out ,
                        CS.Source_Cust_ID ,
                        CS.First_Name ,
                        CS.Last_Name ,
                        CONVERT(CHAR(10), GETDATE() - 1, 101) Eff_Date ,
                        '12/31/2199' End_Date ,
                        'y' Current_Flag) AS MERGE_OUT
        WHERE   MERGE_OUT.Action_Out = 'UPDATE';

I know, it’s a little jumbled, but let’s break it into its basic parts:

First, the main insert statement:

INSERT  INTO Customer_Master
SELECT  FIELDS
FROM    (** SQL CODE **)  AS MERGE_OUT
WHERE   MERGE_OUT.Action_Out = 'UPDATE';

That should seem pretty familiar – just a simple insert statement from a source that contains a field (action_out) we can filter on.

Now for the more tricky part, the merge statement:

MERGE Customer_Master CM
                    USING Customer_Source CS
                    ON ( CM.Source_Cust_ID = CS.Source_Cust_ID )
                    WHEN NOT MATCHED 
                        THEN
INSERT           VALUES
                ( CS.Source_Cust_ID ,
                  CS.First_Name ,
                  CS.Last_Name ,
                  CONVERT(CHAR(10), GETDATE() - 1, 101) ,
                  '12/31/2199' ,
                  'y'
                )
                    WHEN MATCHED AND CM.Current_Flag = 'y'
                        AND ( CM.Last_Name <> CS.Last_Name )
                        THEN
 UPDATE          SET
                CM.Current_Flag = 'n' ,
                CM.End_date = CONVERT(CHAR(10), GETDATE() - 2, 101)
                    OUTPUT
                        $Action Action_Out ,
                        CS.Source_Cust_ID ,
                        CS.First_Name ,
                        CS.Last_Name ,
                        CONVERT(CHAR(10), GETDATE() - 1, 101) Eff_Date ,
                        '12/31/2199' End_Date ,
                        'y' Current_Flag

OK, still a little confusing so I’m going to describe what’s going on in plain English but first a quick note:

The current_flag field was added to the SCD so that an index could be created on it for real time reporting rather than spending the time searching for the current record through the detailed history. This is one of those cases that knowing what a customer really wants, regardless of what they ask for can make for simple performance tuning.

Now for the logic of the “Joe on the street” description of the merge statement:

MERGE Customer_Master CM
                    USING Customer_Source CS
                    ON ( CM.Source_Cust_ID = CS.Source_Cust_ID )

I am going to attempt to merge data into the Customer Master table from the Customer Source table where the ID values in each table match.

WHEN NOT MATCHED 
                        THEN
INSERT           VALUES
                ( CS.Source_Cust_ID ,
                  CS.First_Name ,
                  CS.Last_Name ,
                  CONVERT(CHAR(10), GETDATE() - 1, 101) ,
                  '12/31/2199' ,
                  'y'
                )

If I do not find a match in the customer source table I am going to insert a row with the values I require and move to the next record.

WHEN MATCHED AND CM.Current_Flag = 'y'
                        AND ( CM.Last_Name <> CS.Last_Name )
                        THEN
 UPDATE          SET
                CM.Current_Flag = 'n' ,
                CM.End_date = CONVERT(CHAR(10), GETDATE() - 2, 101)

If I do find a match, and the current_flag is ‘y’ and the last names do not match, then I want to update the Master table with the new values and then move to the next record.

Every other match/no match scenario in this sample, I don’t need so there are no further checks done.

OUTPUT
                        $Action Action_Out ,
                        CS.Source_Cust_ID ,
                        CS.First_Name ,
                        CS.Last_Name ,
                        CONVERT(CHAR(10), GETDATE() - 1, 101) Eff_Date ,
                        '12/31/2199' End_Date ,
                        'y' Current_Flag;

When I’m done with the merge, I want to access the internal table and pass certain values out to any other statements. Note the $Action field in the output, this is what tells us the action the merge statement performed and will be what is used on the outer insert statement to perform the new record insert.

So putting it all together you get this:

INSERT INTO Customer_Master
SELECT 
Source_Cust_ID, 
First_Name, 
Last_Name, 
Eff_Date, 
End_Date, 
Current_Flag
FROM
( MERGE Customer_Master CM
USING Customer_Source CS
ON (CM.Source_Cust_ID = CS.Source_Cust_ID)
WHEN NOT MATCHED THEN
INSERT VALUES (CS.Source_Cust_ID, CS.First_Name, CS.Last_Name,
convert(char(10), getdate()-1, 101), '12/31/2199', 'y')
WHEN MATCHED AND CM.Current_Flag = 'y' AND (CM.Last_Name <> CS.Last_Name ) THEN
 UPDATE SET CM.Current_Flag = 'n', CM.End_date = convert(char(10), getdate()-2, 101)
OUTPUT $Action Action_Out, 
CS.Source_Cust_ID, 
CS.First_Name, 
CS.Last_Name,
convert(char(10), getdate()-1, 101) Eff_Date, 
'12/31/2199' End_Date, 
'y'Current_Flag) 
AS MERGE_OUT
WHERE MERGE_OUT.Action_Out = 'UPDATE';

Hopefully this will help you see just how powerful the merge statement in SQL Server can be.

By using this process we streamlined not only our code base for the data warehouse being built, but also the performance of maintaining a Type 2 SCD.

Helpful links:

SQL Merge Statement: http://technet.microsoft.com/en-us/library/bb510625.aspx

SCD Types: http://www.folkstalk.com/2012/03/slowly-changing-dimensions-scd-types.html

The post Type 2 SCD with SQL Merge appeared first on WaterOx Consulting, Inc.