使用MERGE语句实现SQL Server SCD Type 2的问题求助

在SQL Server中有两张表：

• Source表：包含ID、attribute1、attribute2、attribute3列
• Target表：包含与Source相同的列，额外增加valid_from和valid_to两列

需要实现SCD Type 2逻辑：

1. 若ID存在于Source但不存在于Target，则将该行插入Target，设置valid_from为当前日期，valid_to为'9999-12-31'；
2. 若ID存在且属性值一致，则不做任何操作；
3. 若ID存在但属性值不一致，则将Target中对应行的valid_to设为昨日日期，再将Source中的该行插入Target，设置valid_from为当前日期，valid_to为'9999-12-31'。

尝试用MERGE语句结合更新和插入操作，代码如下：

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'
    );

但该代码无法运行，因为WHEN MATCHED子句无法同时执行更新和插入操作。请问是否有办法让该MERGE语句生效，或是需要拆分为单独的更新和插入操作？

方式一：拆分为独立的更新和插入操作 #

这是最直观且易维护的方案，分两步执行：

1. 更新Target中属性变化的有效记录
   将属性发生变化的当前有效记录（valid_to='9999-12-31'）标记为失效：

UPDATE tgt
SET valid_to = DATEADD(day, -1, CAST(GETDATE() AS DATE))
FROM [Target] tgt
JOIN [Source] src ON tgt.id = src.id
WHERE tgt.valid_to = '9999-12-31'
AND (
    -- 注意：若属性允许NULL，需用ISNULL/COALESCE处理，比如ISNULL(tgt.attribute1, '') <> ISNULL(src.attribute1, '')
    tgt.attribute1 <> src.attribute1
    OR tgt.attribute2 <> src.attribute2
    OR tgt.attribute3 <> src.attribute3
);

2. 插入新记录（含新ID和属性变化的ID）
   插入Source中不存在于Target的新ID，以及属性变化后需要新增的有效记录：

INSERT INTO [Target] (id, attribute1, attribute2, attribute3, valid_from, valid_to)
SELECT 
    src.id,
    src.attribute1,
    src.attribute2,
    src.attribute3,
    CAST(GETDATE() AS DATE),
    '9999-12-31'
FROM [Source] src
LEFT JOIN [Target] tgt ON src.id = tgt.id AND tgt.valid_to = '9999-12-31'
WHERE tgt.id IS NULL -- 新ID
OR (
    tgt.id IS NOT NULL
    AND (
        tgt.attribute1 <> src.attribute1
        OR tgt.attribute2 <> src.attribute2
        OR tgt.attribute3 <> src.attribute3
    )
);

方式二：用MERGE结合OUTPUT实现（单逻辑单元） #

如果希望用接近单语句的方式完成，可以通过MERGE更新失效旧记录，同时捕获需要插入的新记录，再统一插入：

-- 临时表存储需要插入的属性变化记录
DECLARE @NewRecords TABLE (
    id INT,
    attribute1 VARCHAR(50),
    attribute2 VARCHAR(50),
    attribute3 VARCHAR(50),
    valid_from DATE,
    valid_to DATE
);

-- 执行MERGE更新，并输出需要新增的记录到临时表
MERGE INTO [Target] AS tgt
USING [Source] AS src
ON tgt.id = src.id AND tgt.valid_to = '9999-12-31'
WHEN MATCHED AND (
    tgt.attribute1 <> src.attribute1
    OR tgt.attribute2 <> src.attribute2
    OR tgt.attribute3 <> src.attribute3
) THEN
    UPDATE SET tgt.valid_to = DATEADD(day, -1, CAST(GETDATE() AS DATE))
    OUTPUT 
        src.id,
        src.attribute1,
        src.attribute2,
        src.attribute3,
        CAST(GETDATE() AS DATE),
        '9999-12-31'
    INTO @NewRecords;

-- 插入新ID记录+属性变化的新记录
INSERT INTO [Target] (id, attribute1, attribute2, attribute3, valid_from, valid_to)
SELECT 
    src.id,
    src.attribute1,
    src.attribute2,
    src.attribute3,
    CAST(GETDATE() AS DATE),
    '9999-12-31'
FROM [Source] src
LEFT JOIN [Target] tgt ON src.id = tgt.id AND tgt.valid_to = '9999-12-31'
WHERE tgt.id IS NULL
UNION ALL
SELECT id, attribute1, attribute2, attribute3, valid_from, valid_to FROM @NewRecords;

关键注意事项 #

• NULL值处理：若属性列允许NULL，直接用<>比较会返回未知结果，需用ISNULL或COALESCE统一NULL的比较逻辑
• 日期格式：优先用CAST(GETDATE() AS DATE)代替字符串转换，避免格式兼容问题
• 性能优化：大表场景下，确保id和valid_to列有合适的索引，避免全表扫描

内容的提问来源于stack exchange，提问作者br.nz