MERGE/INSERT/DELETE SQL 命令中的多个 OUTPUT 子句? [英] Multiple OUTPUT clauses in MERGE/INSERT/DELETE SQL commands?

问题描述

我有一个 T-SQL 脚本，它使用 MERGE 和 INSERT 中的 OUTPUT 子句实现了一些同步逻辑>s.

I have a T-SQL script that implements some synchronization logic using OUTPUT clause in MERGEs and INSERTs.

现在我要在它上面添加一个日志层，我想添加第二个 OUTPUT 子句以将值写入报告表中.

Now I am adding a logging layer over it and I would like to add a second OUTPUT clause to write the values into a report table.

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

I can add a second OUTPUT clause to my MERGE statement:

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;

这有效，但只要我尝试添加目标

And this works, but as long as I try to add the target

INTO @report_table;

我在 INTO 之前收到以下错误信息:

I get the following error message before INTO:

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

我在这里找到了一个类似的问题，但它并没有进一步帮助我，因为我要插入的字段不会在两个表之间重叠，而且我不想修改工作同步逻辑(如果可能).

I found a similar question here, but it didn't help me further, because the fields I am going to insert do not overlap between two tables and I don't want to modify the working sync logic (if possible).

更新:

在 Martin Smith 的回答之后，我有了另一个想法并重新编写了我的查询如下:

After the answer by Martin Smith I had another idea and re-wrote my query as following:

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.Name, inserted.Code
) M

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

Unfortunately this approach did not work either, the following error message is output at runtime:

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

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

So, there is definitely no way to have multiple OUTPUT clauses in a single DML statement.

推荐答案

不可能.请参阅语法.

Merge 语句具有

[ <output_clause> ]

方括号表明它可以有一个可选的输出子句.语法是

The square brackets show it can have an optional output clause. The grammar for that is

<output_clause>::=
{
    [ OUTPUT <dml_select_list> INTO { @table_variable | output_table }
        [ (column_list) ] ]
    [ OUTPUT <dml_select_list> ]
}

这个子句可以有一个OUTPUT INTO 和一个OUTPUT，但不能有两个相同的.

This clause can have both an OUTPUT INTO and an OUTPUT but not two of the same.

如果允许多个，则语法将具有 [ ,...n ]

If multiple were allowed the grammar would have [ ,...n ]

这篇关于MERGE/INSERT/DELETE SQL 命令中的多个 OUTPUT 子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！