OUTPUT clause in SQL SERVER.

Output is a powerful feature that enables you to eliminate use of triggers in some cases, Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem.

use tempdb;
go
create table itest ( i int identity not null primary key, j int not null unique );
go
create trigger insert_itest on itest after insert
as
begin
insert into #new ( i, j )
select i, j
from inserted;
end
go
create table #new ( i int not null, j int not null );
insert into itest ( j )
select o.id from sysobjects as o;
Newly inserted rows and identity values:
select * from #new;
#new can be used now to insert into a related table:
drop table #new, itest;
go

This code can be re-written using the OUTPUT clause like below:

create table itest ( i int identity not null primary key, j int not null unique )
create table #new ( i int not null, j int not null)
insert into itest (j)
output inserted.i, inserted.j into #new
select o.object_id from sys.objects as o
select * from #new
drop table #new, itest;
go
Now from this example, you can see the integration of OUTPUT clause with existing DML

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s