Updating a Type-2 Dimension with the MERGE command

Question

I am managing a data warehouse. I have several dimension tables most of which are type-2 and some are type-1.

I was able to figure out how to use MERGE to maintain my type-1 dimension tables.

I am stumped on how to do the type-2 dimension tables.

How do I do both, the update to the EndDate and an insert using the MERGE Command?

Answer 1

MERGE will, unfortunately, not let you apply multiple actions to one condition, or let you operate multiple times on one row.

So, what I do is use an INSERT INTO wrapping up the MERGE:

INSERT INTO DimTable
SELECT ID, Name, StartDate, null as EndDate
FROM
    (
        MERGE DimTable AS tgt
        USING StageTable AS src
            ON tgt.id = src.id AND tgt.enddate IS NULL
        WHEN MATCHED THEN
            UPDATE SET tgt.enddate = src.startdate
        WHEN NOT MATCHED THEN
            INSERT INTO (ID, Name, StartDate) 
                VALUES (src.id, src.name, src.startdate)
        OUTPUT $action as Action, src.ID, src.Name, src.StartDate
    ) a

WHERE
    a.Action = 'UPDATE'

The key there is the OUTPUT clause which will actually redirect those rows so you know exactly what happened to them. A brilliant feature, really.