CODEWITHSUNDEEP
sql-servermerge
LearningJrDev
LearningJrDev

Reputation: 941

Multiple OUTPUTs with MERGE

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

Upvotes: 2

Views: 410

Answers (1)

Brad D
Brad D

Reputation: 762

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.

Upvotes: 2

Related Questions