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 ]

Answer 2

Martin Smith is right, it is not possible to have two OUTPUT INTO clauses in one MERGE statement, but he is also right that it is possible to have one OUTPUT INTO and one OUTPUT clause. OUTPUT INTO inserts its result set directly into the given table and the simple OUTPUT returns result set to the caller.

So, you can wrap the MERGE statement into a stored procedure and then use INSERT ... EXEC to insert result set of the simple OUTPUT into a second table.

CREATE PROCEDURE [dbo].[TestMerge]
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    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 MergeAction, inserted.Name, inserted.Code;
END

Usage

INSERT INTO report_table
EXEC [dbo].[TestMerge];

This will insert rows into sync_table and into report_table.

If you examine execution plan you'll see that INSERT ... EXEC creates a temporary table behind the scenes (see also The Hidden Costs of INSERT EXEC by Adam Machanic).

Answer 3

The OUTPUT clause allows for a selectable list. While this doesn't allow for multiple result sets, it does allow for one result set addressing all actions.

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

I overlooked this myself until just the other day, when I needed to know the action taken for the row didn't want to have complicated logic downstream. The means you have a lot more freedom here. I did something similar to the following which allowed me to use the output in a simple means:

DECLARE @MergeResults TABLE (
    MergeAction VARCHAR(50),
    rowId INT NOT NULL,
    col1 INT NULL,
    col2 VARCHAR(255) NULL
    )

MERGE INTO TARGET_TABLE AS t
    USING SOURCE_TABLE AS s
    ON t.col1 = s.col1
WHEN MATCHED
    THEN
        UPDATE
        SET [col2] = s.[col2]
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            [col1]
            ,[col2]
            )
        VALUES (
            [col1]
            ,[col2]
            )
WHEN NOT MATCHED BY SOURCE
    THEN
        DELETE
OUTPUT $action as MergeAction, 
    CASE $action 
        WHEN 'DELETE' THEN deleted.rowId 
        ELSE inserted.rowId END AS rowId,
    CASE $action 
        WHEN 'DELETE' THEN deleted.col1 
        ELSE inserted.col1 END AS col1,
    CASE $action 
        WHEN 'DELETE' THEN deleted.col2 
        ELSE inserted.col2 END AS col2
    INTO @MergeResults;

You'll end up with a result set like:

| MergeAction | rowId | col1 | col2 |
| INSERT      | 3     | 1    | new  |
| UPDATE      | 1     | 2    | foo  |
| DELETE      | 2     | 3    | bar  |

Answer 4

Sorry to resurrect an old thread, but I just ran into this issue and used a solution that's practical rather than technical, and may or may not be obvious.

As already discussed, MERGE isn't designed to do this. The INSERT_INTO...EXEC solution is a good workaround, but the particular stored procedure on which I'm working is already complex enough.

So to keep things simple for the next guy who has to work on this code, I just used two MERGE statements...one that does the inserting and one that does the updating. After all, there's no law that says you have to use only one. I added an "action" column to the logging table, into which I have the MERGE statement insert either "Insert" or "Update" depending on what it's doing.

Performance doesn't take enough of a hit to worry about, especially since this isn't a user process.

TIP: Do the update first, and the insert second. Otherwise, when you do the first load, you'll get one insert record and one update record for every row you import.