sql - Implementing SCD Type 2 with merge statement - Stack Overflow

I have two tables in SQL server, the first table Source, has two columns: ID, attribute1, attribute2, and attribute3. The second table, Target, has the same columns as Source but with two additional columns: valid_from and valid_to.

I want to implement the following logic for my tables in SQL Server:

If the ID does not exists in Source but not in Target, then insert the row in Target, setting valid_from to today's date, and valid_to to '9999-12-31'.

If the ID does exist but attribute is the same, nothing needs to happen.

If the ID does exists but the attribute is not the same. Then I want to update the row in Target, by setting the valid_to to yesterdays date. And then I want to insert the row from Source to target setting valid_from to todays date, and valid_to to '9999-12-31'.

I've tried to combine an update and insert into a merge statement:

MERGE INTO [Target] AS tgt
USING [Source] AS src
ON tgt.id = src.id
WHEN MATCHED AND (
    tgt.attribute1 <> src.attribute1 OR
    tgt.attribute2 <> src.attribute2 OR
    tgt.attribute3 <> src.attribute3
) THEN
    -- Close the current record and insert a new record
    UPDATE SET
        tgt.valid_to = FORMAT(DATEADD(day, -1, GETDATE()), 'yyyy-MM-dd')

    OUTPUT 
        src.id,
        src.attribute1,
        src.attribute2,
        src.attribute3,
        CONVERT(VARCHAR(10), GETDATE(), 120), 
        '9999-12-31'
    INTO norm.Moment (
        id, 
        attribute1, 
        attribute2, 
        attribute3,
        valid_from,
        valid_to
    )
WHEN NOT MATCHED THEN
    -- Insert the new record
    INSERT (
        id, 
        attribute1, 
        attribute2, 
        attribute3,
        valid_from,
        valid_to
    )
    VALUES (
        src.id,
        src.attribute1,
        src.attribute2,
        src.attribute3,
        CONVERT(VARCHAR(10), GETDATE(), 120), 
        '9999-12-31'
    );

But this does not work as the WHEN MATCH won't do both an update and insert in the same THEN clause.

Is there a way to make this kind of merge statement work or should I split it up in some way with seperate insert and updates?

Thanks in advance for any help and let me know if I can clarify things :)

I have two tables in SQL server, the first table Source, has two columns: ID, attribute1, attribute2, and attribute3. The second table, Target, has the same columns as Source but with two additional columns: valid_from and valid_to.

I want to implement the following logic for my tables in SQL Server:

If the ID does not exists in Source but not in Target, then insert the row in Target, setting valid_from to today's date, and valid_to to '9999-12-31'.

If the ID does exist but attribute is the same, nothing needs to happen.

If the ID does exists but the attribute is not the same. Then I want to update the row in Target, by setting the valid_to to yesterdays date. And then I want to insert the row from Source to target setting valid_from to todays date, and valid_to to '9999-12-31'.

I've tried to combine an update and insert into a merge statement:

MERGE INTO [Target] AS tgt
USING [Source] AS src
ON tgt.id = src.id
WHEN MATCHED AND (
    tgt.attribute1 <> src.attribute1 OR
    tgt.attribute2 <> src.attribute2 OR
    tgt.attribute3 <> src.attribute3
) THEN
    -- Close the current record and insert a new record
    UPDATE SET
        tgt.valid_to = FORMAT(DATEADD(day, -1, GETDATE()), 'yyyy-MM-dd')

    OUTPUT 
        src.id,
        src.attribute1,
        src.attribute2,
        src.attribute3,
        CONVERT(VARCHAR(10), GETDATE(), 120), 
        '9999-12-31'
    INTO norm.Moment (
        id, 
        attribute1, 
        attribute2, 
        attribute3,
        valid_from,
        valid_to
    )
WHEN NOT MATCHED THEN
    -- Insert the new record
    INSERT (
        id, 
        attribute1, 
        attribute2, 
        attribute3,
        valid_from,
        valid_to
    )
    VALUES (
        src.id,
        src.attribute1,
        src.attribute2,
        src.attribute3,
        CONVERT(VARCHAR(10), GETDATE(), 120), 
        '9999-12-31'
    );

But this does not work as the WHEN MATCH won't do both an update and insert in the same THEN clause.

Is there a way to make this kind of merge statement work or should I split it up in some way with seperate insert and updates?

Thanks in advance for any help and let me know if I can clarify things :)

• sql
• sql-server
• scd2

Share Improve this question Follow edited Jan 29 at 19:39 Dale K 27.5k1515 gold badges5858 silver badges8383 bronze badges asked Jan 29 at 12:34 br.nzbr.nz 4577 bronze badges 3

• 2 MERGE can't do multiple actions per row, you would need separate statements. Have you considered using Temporal Tables, which does all this for you? – Charlieface Commented Jan 29 at 12:37
• @Charlieface, your comment could be posted as an answer. – Guillaume Outters Commented Jan 29 at 13:35
• We need a complete minimal reproducible example with sample data and desired results in order to fully understand your requirements. – Dale K Commented Jan 29 at 19:44

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 0

In this case the merge statement may not be the best way to go. While they are very cool, they have documented performance issues and limitations.

If you really want to use this merge statement you can simply add an extra insert before it to accomplish what you want to do like this...

insert into dbo.Target
(
    ID
    ,attribute1
    ,attribute2
    ,attribute3
    ,valid_from
    ,valid_to
)
        select
                    src.ID
                    ,src.attribute1
                    ,src.attribute2
                    ,src.attribute3
                    ,getdate()
                    ,'9999-12-31'
                    
        from
                    dbo.Source  src
            join    dbo.Target  tgt on tgt.ID = src.ID
                                    and
                                        (
                                            tgt.attribute1 <> src.attribute1
                                            or  tgt.attribute2 <> src.attribute2
                                            or  tgt.attribute3 <> src.attribute3
                                        )

A simple solution could be to use a trigger on insert that will update all (theorically at most 1) unclosed entries for the attribute.

This has the advantage that manually inserted entries (circumventing your MERGE) will still let the table consistent.

But then using Temporal Tables is the right suggestion, unless you have portability or educational purpose concerns.

Proof of concept

Here a small implementation that you can see in action in a DBFiddle:

CREATE TRIGGER SourceUpdate ON [Target]
AFTER INSERT
AS
BEGIN
    -- Update old entries.
    UPDATE [Target]
    SET valid_to = FORMAT(DATEADD(day, -1, GETDATE()), 'yyyy-MM-dd')
    FROM Inserted new
    WHERE [Target].id = new.id AND [Target].valid_to = '9999-12-31';
    -- Then give the new entry its definitive valid_to.
    UPDATE [Target]
    SET valid_from = CONVERT(VARCHAR(10), GETDATE(), 120), valid_to = '9999-12-31'
    FROM Inserted new
    WHERE [Target].id = new.id AND [Target].valid_to IS NULL;
END;

-- And instead of the MERGE, use a simple INSERT.
INSERT INTO [Target] (id, attribute1, attribute2, attribute3)
SELECT *
FROM [Source] src
WHERE NOT EXISTS
(
    SELECT 1 FROM [Target] tgt
    WHERE tgt.id = src.id
    AND valid_to = '9999-12-31'
    AND tgt.attribute1 = src.attribute1
    AND tgt.attribute2 = src.attribute2
    AND tgt.attribute3 = src.attribute3
);

Note that (I'm discovering SQL Server so) I had to use two values of valid_to (NULL and 9999-12-31) to distinguish the just added row from the old entries in the trigger.