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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s