使用T-SQL Merge实现SCD Type 2时输出值异常的技术咨询

1. 现象是否正常？ #

是正常行为。

在MERGE语句的WHEN MATCHED THEN UPDATE分支中，输出子句里的deleted对象代表更新前的目标表旧记录，inserted对象代表更新后的目标表记录。你仅更新了meta_load_end_date和meta_active字段，未修改id，因此deleted.id和inserted.id自然都是1——这是MERGE输出子句的既定逻辑：UPDATE操作会同时返回旧值和新值两个结果集，只有INSERT操作才会出现deleted对象为NULL的情况。

2. MERGE是否适用于SCD Type2场景？ #

完全适用，但你的当前脚本未实现完整的SCD Type2逻辑——仅完成了“标记旧记录失效”的步骤，缺少“插入更新/删除后的新记录”的关键操作。

修正思路与示例脚本 #

要实现你的需求，需通过MERGE的输出子句捕获操作信息，再基于这些信息插入新记录：

步骤1：创建临时表捕获MERGE操作结果

DECLARE @MergeOps TABLE (
    ActionFlag NVARCHAR(10),
    TargetID INT,
    SourceID INT,
    RecordName NVARCHAR(100)
);

步骤2：执行MERGE并输出操作数据

MERGE test_rbo AS target
USING test_rbo_source AS source
    ON target.id = source.id AND target.meta_active = 1

-- 处理源表新记录：直接插入
WHEN NOT MATCHED BY target THEN
    INSERT (id, name, meta_load_date, meta_load_end_date, meta_deleted, meta_active, action)
    VALUES (source.id, source.name, GETDATE(), '9999-12-31 23:59:59', NULL, 1, 'INSERT')

-- 处理源表更新记录：先标记旧记录失效，同时输出更新信息
WHEN MATCHED AND target.name <> source.name THEN
    UPDATE SET 
        target.meta_load_end_date = GETDATE(), 
        target.meta_active = 0
    OUTPUT 
        'UPDATE',
        deleted.id,
        source.id,
        source.name
    INTO @MergeOps

-- 处理源表删除记录：先标记旧记录失效，同时输出删除信息
WHEN NOT MATCHED BY source THEN
    UPDATE SET 
        target.meta_load_end_date = GETDATE(), 
        target.meta_active = 0
    OUTPUT 
        'DELETE',
        deleted.id,
        NULL,
        deleted.name
    INTO @MergeOps;

步骤3：插入更新后的新记录

INSERT INTO test_rbo (id, name, meta_load_date, meta_load_end_date, meta_deleted, meta_active, action)
SELECT 
    SourceID,
    RecordName,
    GETDATE(),
    '9999-12-31 23:59:59',
    NULL,
    1,
    'UPDATE'
FROM @MergeOps
WHERE ActionFlag = 'UPDATE';

步骤4：插入带删除标记的新记录

INSERT INTO test_rbo (id, name, meta_load_date, meta_load_end_date, meta_deleted, meta_active, action)
SELECT 
    TargetID,
    RecordName,
    GETDATE(),
    GETDATE(),
    GETDATE(),
    0,
    'DELETE'
FROM @MergeOps
WHERE ActionFlag = 'DELETE';

关键说明 #

• 用临时表捕获MERGE的操作结果，可将“标记旧记录失效”和“插入新记录”两个步骤解耦，确保SCD Type2逻辑的完整性；
• 针对不同操作类型（UPDATE/DELETE）分别处理插入逻辑，精准匹配你的需求细节。

内容的提问来源于stack exchange，提问作者Remco