Output clause

Output clause:

OUTPUT clause has accesses to inserted and deleted tables just like triggers. OUTPUT clause can be used to return values to client clause. OUTPUT clause can be used with INSERT, UPDATE, or DELETE to identify the actual rows affected by these statements.


Let us understand OUTPUT clause using example.

Initial setup:

create table #t
(id int,
p_name char(10)
)

insert into #t values ( 1,'AAAAA')
insert into #t values ( 2,'BBBBB')
insert into #t values ( 3,'CCCCC')
insert into #t values ( 4,'DDDDD')
insert into #t values ( 5,'EEEEE')

select * from #t



Scneario1:

Archive the records into #t_arc table when records get deleted in #t table.

Normal solution is that, insert into #t_arc table before delete the records in the #t table based on the conditions.
create table #t_arc
(id int,
p_name char(10)
)

insert into #t_arc
select * from
where id =3

delete from #t
where id =3
The output clause avoids the execution of the two statements.

delete from #t
output deleted.* into #t_arc
where id =3


One of the very good advantages is eliminating the data access from table for each time. The DELETE … OUTPUT statement access the data once and do the operations.

Scneario2:
We see another example when we insert the data into table.
create table #t_audit
(id int,
load_time datetime
)

The below statement loads the audit table when loads the data into #t table.

insert into #t
output inserted.id,getdate() into #t_audit
values ( 6,'FFFFF')