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

Question

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 1

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 ]