SQL Server中用MERGE实现带变更历史的表更新方案咨询

你的需求是标准SCD Type 2缓慢变化维全历史留存场景，完全可以通过MERGE语句配合OUTPUT子句实现，逻辑可复用到所有同类历史表。
你之前写的代码存在几个核心问题，无法实现预期效果：

• 语法错误：MERGE语句的WHEN MATCHED分支仅支持UPDATE/DELETE操作，不支持直接写INSERT，INSERT逻辑只能放在WHEN NOT MATCHED BY TARGET分支下
• 匹配逻辑缺失：没有区分目标表中已归档的历史行和当前生效行，会把所有历史数据都拉来做匹配
• 操作逻辑不全：既没有做旧版本记录的封版（更新关闭日期），也没有处理「目标存在但源表缺失」的删除标记逻辑，更没有实现「字段变更时插入新版本行」的要求

所有逻辑完全对齐你给出的预期结果，核心规则统一：

1. 仅对目标表当前生效的记录做匹配比对（即DELETE_FLAG='N'、RECORD_UPDATED_DATE='9999-12-31'的行），永不修改已经归档的历史行
2. 主键匹配但指定追踪列的值发生变化：先将旧生效行的关闭日期设为当前执行日期，再插入新版本生效行，新版本标记为有效、截止日期设为9999-12-31
3. 目标存在生效行但源表无对应主键：将该行标记为删除，关闭日期设为当前执行日期
4. 源表存在主键但目标无对应生效行：直接插入新的生效行，标记为有效、截止日期设为9999-12-31

1. 定义通用常量 #

所有SCD2加载场景都可以直接复用这两个变量，无需修改：

-- 本次加载的执行日期，所有封版的旧记录统一使用该时间
DECLARE @ExecuteDate DATE = GETDATE();
-- 生效记录的永久截止日期
DECLARE @MaxDate DATE = '9999-12-31';

2. 准备源数据临时表 #

这里替换成你已经开发完成的多表关联基表逻辑即可：

SELECT      
    [KEY] AS MAIN_KEY,
    CLIENT,
    QUANTITY,
    CONTRACT_NO,
    FC_COUNT
INTO  #G1_DELTA
FROM [dwh].STG_DTL;

3. 创建临时表存储MERGE输出结果 #

用于接收MERGE过程中识别到的需要新增的版本记录，规避MERGE单分支无法同时更新+插入的限制：

CREATE TABLE #MergeOutput (
    ActionType VARCHAR(10),
    MAIN_KEY INT,
    CLIENT VARCHAR(100),
    QUANTITY INT,
    CONTRACT_NO VARCHAR(100),
    FC_COUNT INT,
    DELETE_FLAG CHAR(1),
    RECORD_UPDATED_DATE DATE
);

4. 执行MERGE核心逻辑 #

MERGE阶段仅处理旧记录的状态更新，所有新增行通过OUTPUT导出后统一插入：

MERGE [dwh].[PRJ1_DELTA] AS TARGET
USING (
    SELECT 
        s.MAIN_KEY,
        s.CLIENT,
        s.QUANTITY,
        s.CONTRACT_NO,
        s.FC_COUNT
    FROM #G1_DELTA s
    FULL OUTER JOIN (
        SELECT * FROM [dwh].[PRJ1_DELTA] 
        WHERE DELETE_FLAG = 'N' AND RECORD_UPDATED_DATE = @MaxDate
    ) t ON s.MAIN_KEY = t.MAIN_KEY
) AS SOURCE
ON TARGET.MAIN_KEY = SOURCE.MAIN_KEY 
AND TARGET.DELETE_FLAG = 'N' 
AND TARGET.RECORD_UPDATED_DATE = @MaxDate

-- 场景1：主键匹配，追踪列值发生变化，封旧版本
WHEN MATCHED AND (
    TARGET.CLIENT <> SOURCE.CLIENT
    OR TARGET.QUANTITY <> SOURCE.QUANTITY
    OR TARGET.CONTRACT_NO <> SOURCE.CONTRACT_NO
    OR TARGET.FC_COUNT <> SOURCE.FC_COUNT
)
THEN UPDATE SET
    TARGET.RECORD_UPDATED_DATE = @ExecuteDate,
    TARGET.DELETE_FLAG = 'N'

-- 场景2：目标存在生效行但源表无对应数据，标记删除
WHEN MATCHED AND SOURCE.MAIN_KEY IS NULL
THEN UPDATE SET
    TARGET.RECORD_UPDATED_DATE = @ExecuteDate,
    TARGET.DELETE_FLAG = 'Y'

-- 场景3：源有数据但目标无对应生效行，直接插入新生效记录
WHEN NOT MATCHED BY TARGET AND SOURCE.MAIN_KEY IS NOT NULL
THEN INSERT (
    MAIN_KEY,
    CLIENT,
    QUANTITY,
    CONTRACT_NO,
    FC_COUNT,
    DELETE_FLAG,
    RECORD_UPDATED_DATE
)
VALUES (
    SOURCE.MAIN_KEY,
    SOURCE.CLIENT,
    SOURCE.QUANTITY,
    SOURCE.CONTRACT_NO,
    SOURCE.FC_COUNT,
    'N',
    @MaxDate
)
-- 输出变更产生的新版本记录到临时表
OUTPUT 
    $action AS ActionType,
    SOURCE.MAIN_KEY,
    SOURCE.CLIENT,
    SOURCE.QUANTITY,
    SOURCE.CONTRACT_NO,
    SOURCE.FC_COUNT,
    'N' AS DELETE_FLAG,
    @MaxDate AS RECORD_UPDATED_DATE
INTO #MergeOutput;

5. 插入变更产生的新版本记录 #

INSERT INTO [dwh].[PRJ1_DELTA] (
    MAIN_KEY,
    CLIENT,
    QUANTITY,
    CONTRACT_NO,
    FC_COUNT,
    DELETE_FLAG,
    RECORD_UPDATED_DATE
)
SELECT 
    MAIN_KEY,
    CLIENT,
    QUANTITY,
    CONTRACT_NO,
    FC_COUNT,
    DELETE_FLAG,
    RECORD_UPDATED_DATE
FROM #MergeOutput
WHERE ActionType = 'UPDATE';

6. 清理临时表 #

DROP TABLE IF EXISTS #G1_DELTA, #MergeOutput;

这套逻辑可以直接套用到所有需要保留全量变更历史的表，仅需要修改3处内容：

• 源临时表、目标表的字段列表
• MERGE匹配分支中追踪变更的字段判断条件，把需要监控变化的列加入比对即可
• 主键关联条件，如果是联合主键，把ON后的匹配规则改为多字段关联即可

注意：如果比对的字段存在NULL值，需要把不等值判断改为兼容NULL的写法，例如将TARGET.CLIENT <> SOURCE.CLIENT替换为ISNULL(TARGET.CLIENT,'') <> ISNULL(SOURCE.CLIENT,'')，避免NULL值比对漏判变更。

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