Multiple OUTPUT clauses in MERGE/INSERT/DELETE SQL…

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

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.

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;

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).

UPDATE:

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.

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

Answer

Not possible. See the grammar.

The Merge statement has

[ <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> ]
}

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

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

Multiple OUTPUT clauses in MERGE/INSERT/DELETE SQL commands?

Advertisement

Answer