SQL Server中MERGE语句报'WHEN关键字附近语法错误'求助

错误原因 #

SQL Server的MERGE语句不允许在单个WHEN分支内单独使用OUTPUT子句，你在WHEN MATCHED和WHEN NOT MATCHED BY TARGET分支后分别添加OUTPUT的写法违反了语法规则，这就是触发Msg 156错误的原因。

修正后的SQL代码 #

IF OBJECT_ID('tempdb..#OutputIds') IS NOT NULL DROP TABLE #OutputIds;
CREATE TABLE #OutputIds (Action NVARCHAR(10), ChildId NVARCHAR(MAX));

MERGE INTO LeadShirts AS target
USING (VALUES ('abc', 'abc'), ('abc', 'abc')) AS source (ParentId, ChildId)
ON (target.Lead = source.ParentId AND target.Shirts = source.ChildId)
WHEN MATCHED AND target.Deleted = 1 THEN
    UPDATE SET UpdatedOn = GETDATE(), Deleted = 0  -- Re-activate deleted records
WHEN NOT MATCHED BY TARGET THEN
    INSERT (Lead, Shirts, Deleted, CreatedOn)
    VALUES (source.ParentId, source.ChildId, 0, GETDATE())
-- 统一在MERGE末尾添加OUTPUT子句，区分操作类型
OUTPUT 
    CASE 
        WHEN $action = 'UPDATE' THEN 'Edited'
        WHEN $action = 'INSERT' THEN 'Added'
    END AS Action,
    source.ChildId INTO #OutputIds (Action, ChildId);

-- 保留原有的Skipped记录逻辑
INSERT INTO #OutputIds (Action, ChildId)
SELECT 'Skipped', ChildId
FROM (VALUES ('abc', 'abc'), ('abc', 'abc')) AS V(ParentId, ChildId)
WHERE EXISTS (SELECT 1 FROM LeadShirts AS T WHERE T.Lead = V.ParentId AND T.Shirts = V.ChildId AND T.Deleted IS NULL);

SELECT Action, ChildId FROM #OutputIds;

关键说明 #

• $action是MERGE语句内置的系统变量，会返回当前操作的类型：UPDATE（匹配且执行更新）或INSERT（无匹配且执行插入）。
• 通过CASE语句根据$action的值映射为你需要的Action字段值（'Edited'/'Added'），统一输出到临时表。
• 原有的"Skipped"记录逻辑可以保留，用于捕捉那些已存在且未被删除的记录。

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