SQL Server : Output Clause with UPDATE

declare @audit table(Id int,value int,value2 int,Diff int,value3 int)

create table Test (Id int identity(1,1), value int, value2 int)
create table Test2 (Id int identity(1,1), value int, value2 int,value3 int)

insert Test (value,value2)
values(5,6),(7,8),(9,10),(10,11)
insert Test2 (value,value2,value3)
values (120,130,30),(5,4,0),(7,8,1),(9,3,2),(12,13,3)

select * from Test
image

select * from Test2
image

update Test
set value2 = t.value2 + t2.value2
output inserted.Id, inserted.value,inserted.value2,t2.value2,t2.value3 into @audit(Id,value,value2,Diff,value3)
from Test t
join Test2 t2 on t.value = t2.value

select * from Test
image

select * from Test2
image

select * from @audit
image

Advertisements