如何使用MERGE实现源表匹配时到期目标表旧记录并插入新记录

语法限制说明 #

你没有逻辑疏漏，这是MERGE语法的原生固定规则：

• WHEN MATCHED分支对应源表与目标表存在匹配行的场景，仅允许对目标表中已匹配到的现有行执行UPDATE或DELETE操作，逻辑上是对已有行的修改，因此不支持INSERT（INSERT是新增不存在的行，不属于对已有匹配行的操作范畴）
• 仅WHEN NOT MATCHED BY TARGET分支允许执行INSERT，对应目标表无匹配行需要新增的场景，这是ANSI SQL约定的MERGE语法规范，不是你的实现问题。

兼容MERGE的实现方案 #

你需要的「匹配时先更新旧记录过期、再插入新记录」的需求，可以通过MERGE配合表变量实现，全程仅需2次表扫描，比你原有的3步操作（临时表查询、更新旧记录、插入新记录）性能提升明显，同时可直接处理不匹配行写入错误表的需求，参考代码如下：

-- 1. 声明存储待插入新记录的表变量
DECLARE @ValidNewRecords TABLE (
    action_type VARCHAR(20),
    customerid VARCHAR(100),
    NewProductid VARCHAR(100),
    NewTerritoryId VARCHAR(100),
    Start_Date DATE,
    End_Date DATE
);

-- 2. 执行MERGE完成旧记录过期，同时捕获匹配成功的待插入数据
MERGE INTO B AS Target
USING A AS Source
ON Source.customer = Target.customer 
AND Source.oldproduct = Target.product 
AND Source.oldterritory = Target.territory
-- 匹配到则将旧记录过期
WHEN MATCHED THEN
    UPDATE SET Target.EndDate = Source.StartDate
-- 捕获所有操作的源数据
OUTPUT 
    $action,
    Source.customer,
    Source.newproduct,
    Source.newterritory,
    Source.StartDate,
    Source.EndDate
INTO @ValidNewRecords(action_type, customerid, NewProductid, NewTerritoryId, Start_Date, End_Date);

-- 3. 插入匹配成功的新记录到目标表B
INSERT INTO B (customer, product, territory, StartDate, EndDate)
SELECT 
    customerid,
    NewProductid,
    NewTerritoryId,
    Start_Date,
    End_Date
FROM @ValidNewRecords
WHERE action_type = 'UPDATE'; -- 仅保留匹配成功的记录

-- 4. 不匹配的记录写入错误表
INSERT INTO A_ErrorRecords (customer, oldproduct, newproduct, oldterritory, newterritory, StartDate, EndDate)
SELECT 
    Source.customer,
    Source.oldproduct,
    Source.newproduct,
    Source.oldterritory,
    Source.newterritory,
    Source.StartDate,
    Source.EndDate
FROM A Source
LEFT JOIN B Target 
ON Source.customer = Target.customer 
AND Source.oldproduct = Target.product 
AND Source.oldterritory = Target.territory
WHERE Target.customer IS NULL;

注意事项 #

• 匹配条件需确保唯一性，避免单条源行匹配多条目标行导致更新异常，建议给目标表B的(customer,product,territory)字段加唯一索引
• 可根据实际业务需求在MERGE的WHEN MATCHED分支补充额外过滤条件，比如仅过期EndDate大于当前日期的有效记录

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