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
Post a Comment