SQL使用Merge by target子句更新目标列不符合预期问题求解

• 插入数据源错误：MERGE语句的Inserted.*对于UPDATE操作返回的是更新后的原表旧记录数据，你后续将该数据再次插入t1，导致ID=2出现了两条完全相同的旧记录。
• 匹配到的新记录未插入：MERGE的NOT MATCHED BY TARGET分支只会插入t2中在t1完全无匹配的ID（本次为3、4），匹配成功的ID=2对应的t2新记录没有被插入，不符合“更新旧记录后追加新记录”的需求。
• 匹配逻辑不严谨：当前匹配条件绑定了新旧记录的DT_FINVAL相等，仅适用于本次测试场景，实际业务中应该匹配同ID下当前生效的旧记录，即添加Target.DT_FINVAL = '2050-12-31'条件，避免匹配到已失效的历史记录。

INSERT INTO t1 (ID, DT_DEBVAL, DT_FINVAL)
SELECT Source_ID, Source_DEBVAL, Source_FINVAL
FROM (
    MERGE t1 AS Target
    USING (SELECT ID, DT_DEBVAL, DT_FINVAL FROM t2) AS Source
    -- 匹配同ID下的生效旧记录
    ON Target.ID = Source.ID AND Target.DT_FINVAL = '2050-12-31'
    WHEN MATCHED THEN
        -- 旧记录结束日期设为新记录开始前1天，逻辑更严谨
        UPDATE SET Target.DT_FINVAL = DATEADD(DAY, -1, Source.DT_DEBVAL)
    WHEN NOT MATCHED BY TARGET THEN
        INSERT (ID, DT_DEBVAL, DT_FINVAL)
        VALUES (Source.ID, Source.DT_DEBVAL, Source.DT_FINVAL)
    -- 输出匹配到的源表新记录，用于后续插入
    OUTPUT $ACTION AS Act, Source.ID AS Source_ID, Source.DT_DEBVAL AS Source_DEBVAL, Source.DT_FINVAL AS Source_FINVAL
) AS MergeResult
-- 仅插入匹配更新过的ID对应的新记录
WHERE MergeResult.Act = 'UPDATE';

• t2中无匹配的ID（3、4）直接在MERGE的插入分支完成入库
• t2中有匹配的ID（2）先更新旧记录的结束日期，再通过外层INSERT将t2中的新记录追加到t1，完全符合需求
• 结束日期取新记录开始日期减1，而不是当前日期减1，避免跑批日期延迟导致的日期错误

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