"Quick Learn": IT tips by experts

Microsoft => SQL Server => Topic started by: Ritesh Shah on October 09, 2012, 11:19:35 AM

Title: OUTPUT Clause in SQL Server
Post by: Ritesh Shah on October 09, 2012, 11:19:35 AM
OUTPUT clause is one of the handy TSQL enhancement which helps you to maintain history. Before SQL Server 2005, it was very difficult to maintain change tracking. here is small example:

Code: [Select]
–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

for more details, have a look at following two articles:

http://blog.extreme-advice.com/2011/07/04/audit-trail-with-output-clause-in-sql-server-20052008denali/ (http://blog.extreme-advice.com/2011/07/04/audit-trail-with-output-clause-in-sql-server-20052008denali/)

Code: [Select]
http://blog.extreme-advice.com/2009/08/17/output-clause-in-sql-server-20082005/