OUTPUT Clause in SQL Server 2008/2005

Today I was working on one Audit Trail project and thought to test OUTPUT clause. As soon as I wrote OUTPUT clause, it pops up in my mind that I have not written anything about OUTPUT clause in my blog so far. So, I felt, this is the time to write something about this.
You people might be aware with pseudo table (INSERTED and DELETED) in trigger, this is something similar concept, the difference is, you can get pseudo table inside trigger and you can use OUTPUT clause outside trigger too.
Let us see its usefulness by one small example.
–create table for demo and insert few records
create table testOutPut(tid int identity(1,1), name varchar(50))
insert into testOutPut (name)
select ‘Ritesh’ union all
select ‘Rajan’ union all
select ‘Pinal’ union all
select ‘Bihag’ union all
select ‘John’ union all
select ‘Bhaumik’ union all
select ‘Avi’ union all
select ‘James’
go
–check whether all records came with proper tid
select * from testOutPut
go
–create temp duplicate table which will store data which are deleted
create table #deleted (id int, name varchar(50))
–deleting records from testOutPut table and inserting those deleted
–records into temp table with help of OUTPUT clause and DELETED pseudo table
delete testOutPut
output DELETED.* into #deleted
where tid < 3
–checking temp table
select * from #deleted
–this will show deleted data on screen
–but won’t store anywhere like we did in previous snippet
–and stored data in #deleted (temp table)
delete testOutPut
output DELETED.*
where tid >3
select * from testOutPut
go
select * from #deleted
go
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “OUTPUT Clause in SQL Server 2008/2005”

Comments are closed.