Clever Geek Handbook

Using both INSERTED and DELETED from and OUTPUT

At my request UPDATE

, I would like to save the values deleted

and inserted

in some database tables.

So my request should look something like this:

UPDATE A_TABLE
  SET table_column = 'something'
    OUTPUT deleted.*
    INTO audit.deleted_content

    OUTPUT inserted.*
    INTO audit.inserted_content
  WHERE blah = 'something else'

However, I am getting the problem in the second key INTO

(incorrect syntax next to 'in'). Is there a correct way to accomplish the above?

+3


source to share


1 answer


You can use a temporary table to store the results and do your inserts like this:

create table #audit (
    deleted_table_column varchar(32)
  , inserted_table_column varchar(32)
)

update A_table
  set table_column = 'something'
    output deleted.table_column, inserted.table_column
    into #audit
  where blah = 'something else'


insert into audit.deleted_content
select deleted_table_column from #audit;

insert into audit.inserted_content
select inserted_table_column from #audit;


Demo version of the rexter: http://rextester.com/BVSFRJ92976

+2


source






More articles:


All Articles