sql server - Multiple OUTPUTs with MERGE -


i have merge looks likes this:

merge target_table t using source_table s     on t.local_id = s.local_id when matched     , (         t.[col1] <> s.[col1]         or t.[col2] <> s.[col2]         or t.[col5] <> s.[col5]         )             update         set [col1] = s.[col1]             ,[col2] = s.[col2]             ,[col5] = s.[col5] when not matched target             insert (             [local_id]             ,[col1]             ,[col2]             ,[col5]             )         values (             s.[local_id]             ,[col1]             ,[col2]             ,[col5]             ) when not matched source             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 [audit]; 

which pushes row audit table looks this:

local_id action col1 col2 col5 new_col1 new_col2 new_col5

with demo values:

123 update foo bar foobar foo bar foobar

but i'd like

123 update new foo bar foobar 123 update old foo bar foobar 

where output of merge has 1 row deleted values , 1 row inserted values.

my first attempt @ use output , output into , wrap insert (see multiple output clauses in merge/insert/delete sql commands? ) sql server says cannot error an output clause not allowed in nested insert, update, delete, or merge statement.

any suggestions on how accomplish want without getting messy? hoping newer sql server software addresses in way. using sql server enterprise 2012

in output statement include both inserted columns , deleted columns.

output getdate(), $action deleted.col1 oldcol1, inserted.col1 newcol1 #temptable... 

then unpivot or cross apply linking columns new , old values.


Comments