SQL 2008 Merge statement for SCD type 2 implementation

USE Merge statement for SCD type 2 implementation

 One of the new T-SQL features in SQL 2008 is the Merge statement.

There are 3 separate matching clauses you can specify. The 3 matching clauses are:

• o WHEN MATCHED
• o WHEN NOT MATCHED BY TARGET
• o WHEN NOT MATCHED BY SOURCE

This statement can be used to implement a procedure for a slowly changing dimension type 2.

The source below contains an example of an implementation

CREATE PROC SCD2

(

@Startdate datetime

)

INSERT    <Target dimension>

        (KeyCol1,Col2,Startdate,Enddate)

SELECT   KeyCol1,Col2,@Startdate,’dec 31 2999′

FROM

(

MERGE <Target dimension> AS target

USING (SELECT

                   <SELECTION FROM SOURCE>

) AS source

ON (target.KEYCOl1 = source.KeyCol1

          and target. Enddate = ‘dec 31 2999’)

WHEN MATCHED AND  (source.Col2 <> target. Col2) THEN

          UPDATE SET Enddate = @Startdate

WHEN NOT MATCHED BY TARGET THEN

          INSERT (Keycol1, Col2,Startdate,Enddate)

          VALUES (KeyCol1, Col2,@Startdate,’dec 31 2999′)

          OUTPUT

                   $action,

                   source. KEYCOl1,

                   source. Col2

) AS Output (output_action, KEYCOl1, Col2)

WHERE output_action = ‘UPDATE’

END