现在我正在添加一个日志记录层,我想添加一个第二个OUTPUT子句来将值写入报表.

我可以在我的MERGE语句中添加第二个OUTPUT子句：

MERGE TABLE_TARGET AS T
USING TABLE_SOURCE AS S
ON (T.Code = S.Code) 
WHEN MATCHED AND T.IsDeleted = 0x0
    THEN UPDATE SET ....
WHEN NOT MATCHED BY TARGET 
    THEN INSERT ....
OUTPUT inserted.sqlId,inserted.IncId
INTO @sync_table
OUTPUT $action,inserted.Name,inserted.Code;

而这样做,但只要我尝试添加目标

INTO @report_table;

在INTO之前收到以下错误消息：

A MERGE statement must be terminated by a semicolon (;)

我发现了a similar question here,但是它没有帮助我进一步,因为我要插入的字段不会在两个表之间重叠,我不想修改工作同步逻辑(如果可能的话).

更新：

在Martin Smith的答案之后,我有另一个想法,并重写了我的查询如下：

INSERT INTO @report_table (action,name,code)
SELECT M.Action,M.Name,M.Code
FROM
(
MERGE TABLE_TARGET AS T
USING TABLE_SOURCE AS S
ON (T.Code = S.Code) 
WHEN MATCHED AND T.IsDeleted = 0x0
    THEN UPDATE SET ....
WHEN NOT MATCHED BY TARGET 
    THEN INSERT ....
OUTPUT inserted.sqlId,inserted.IncId
INTO @sync_table
OUTPUT $action as Action,inserted.Code
) M

不幸的是,这种方法也不起作用,在运行时输出以下错误消息：

An OUTPUT INTO clause is not allowed in a nested INSERT,UPDATE,DELETE,or MERGE statement.

所以,绝对没有办法在单个DML语句中有多个OUTPUT子句.