Multiple OUTPUTs with MERGE

Question

I have a merge that looks likes this:

MERGE INTO TARGET_TABLE AS t
USING SOURCE_TABLE AS s
    ON t.LOCAL_ID = s.LOCAL_ID
WHEN MATCHED
    AND (
        t.[col1] <> s.[col1]
        OR t.[col2] <> s.[col2]
        OR t.[col5] <> s.[col5]
        )
    THEN
        UPDATE
        SET [col1] = s.[col1]
            ,[col2] = s.[col2]
            ,[col5] = s.[col5]
WHEN NOT MATCHED BY TARGET
    THEN
        INSERT (
            [LOCAL_ID]
            ,[col1]
            ,[col2]
            ,[col5]
            )
        VALUES (
            s.[LOCAL_ID]
            ,[col1]
            ,[col2]
            ,[col5]
            )
WHEN NOT MATCHED BY SOURCE
    THEN
        DELETE
OUTPUT GetDate()
    ,s.LOCAL_ID
    ,$ACTION
    ,deleted.[col1] col1
    ,deleted.[col2] col2
    ,deleted.[col5] col5
    ,inserted.[col1] NEW_col1
    ,inserted.[col2] NEW_col2
    ,inserted.[col5] NEW_col5
INTO [AUDIT];

Which pushes a row into my AUDIT table that looks like this:

LOCAL_ID ACTION col1 col2 col5 NEW_col1 NEW_col2 NEW_col5

with some demo values:

123 UPDATE foo bar foobar FOO BAR FOOBAR

but what I'd really like it something like

123 UPDATE NEW FOO BAR FOOBAR
123 UPDATE OLD foo bar foobar

Where the output of the merge has one row for deleted values and one row for inserted values.

My first attempt at this was to use OUTPUT and OUTPUT INTO and then wrap it with an INSERT (See Multiple OUTPUT clauses in MERGE/INSERT/DELETE SQL commands? ) but SQL server says I cannot do this with error An OUTPUT INTO clause is not allowed in a nested INSERT, UPDATE, DELETE, or MERGE statement.

Any suggestions on how to accomplish what I want without getting very messy? I am hoping that the newer SQL server software addresses this in some way. I am using SQL SERVER Enterprise 2012

Does it have to all be done in one merge? I'm thinking (if this is the only way the table is updated), this may be more naturally done within a trigger. — Damien_The_Unbeliever, Jul 10 '15 at 16:50
You might be careful using MERGE. There are a number of bugs with that statement. https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ — Sean Lange, Jul 10 '15 at 16:51
@SeanLange almost all bugs are fixed. Aaron had a point but I think 7 years and 3 major releases after MERGE it's time to let this rest. — usr, Jul 10 '15 at 22:16
Almost all of the bugs except for things like upsert causing deadlocks which is probably the most common usage of MERGE, including the script posted here. — Sean Lange, Jul 13 '15 at 13:59

score 2 · Accepted Answer · answered Jul 10 '15 at 21:46

In your output statement Include both Inserted columns and Deleted columns.

OUTPUT GETDATE(),
$Action
Deleted.Col1 as OldCol1,
Inserted.Col1 as NewCol1
INTO #tempTable...

Then do an Unpivot or Cross Apply linking the columns to the new and old values.

Multiple OUTPUTs with MERGE

1 Answers1