MERGE语句中基于源表的不同删除输出行为问题及解决

错误原因 #

SQL Server对MERGE语句的语法有严格限制：同一个MERGE语句中，WHEN MATCHED类型的DELETE操作只能定义一次。你写了两个WHEN MATCHED的DELETE分支（分别对应source.state = 0和source.state = -1），哪怕其中一个带OUTPUT子句，本质上都是WHEN MATCHED触发的DELETE操作，违反了这个规则，所以报错。另外要注意：OUTPUT子句属于整个MERGE语句，不能单独绑定到某个分支的DELETE操作上。

实现方案 #

我们可以把两种DELETE场景合并成一个WHEN MATCHED分支，然后通过OUTPUT的WHERE条件筛选，只把需要迁移（source.state = -1）的删除行插入到目标表。修改后的完整代码如下：

CREATE TABLE #data 
(
    id int IDENTITY(1,1),
    description nvarchar(100) NOT NULL,
    metadata int NULL
)

CREATE TABLE #anotherTable 
(
    id int identity(1,1),
    description nvarchar(100) NOT NULL
)

INSERT INTO #data (description)
VALUES (N'data'),
       (N'more data'),
       (N'example'),
       (N'unknown')

CREATE TABLE #metadata 
(
    id int IDENTITY(1,1),
    description nvarchar(100) NOT NULL,
    metadata int NOT NULL,
    state int NOT NULL DEFAULT -1
)

INSERT INTO #metadata (description, metadata, state)
VALUES (N'data', 10, 1),
       (N'more data', 11, 0),
       (N'example', 12, -1)

-- 修改后的MERGE语句
MERGE #data AS target
USING #metadata AS source
      ON (target.description = source.description)
WHEN NOT MATCHED BY SOURCE THEN 
    DELETE
WHEN MATCHED AND source.state = 1 THEN
    UPDATE SET target.metadata = source.metadata
WHEN MATCHED AND (source.state = 0 OR source.state = -1) THEN
    DELETE
-- 仅将state=-1的删除行输出到#anotherTable
OUTPUT deleted.description 
INTO #anotherTable (description)
WHERE source.state = -1;


SELECT * FROM #data
SELECT * FROM #anotherTable
SELECT * FROM #metadata

DROP TABLE #data, #metadata, #anotherTable

逻辑说明 #

1. 合并source.state = 0和source.state = -1的DELETE分支，符合MERGE的语法要求；
2. 通过OUTPUT子句后的WHERE source.state = -1，只把需要迁移的删除行插入到#anotherTable，而source.state = 0的行仅被删除，不会进入迁移表；
3. 保留原有的更新和未匹配源数据的删除逻辑。

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