Author Topic: OUTPUT Clause in SQL Server  (Read 2356 times)

Ritesh Shah

  • Administrator
  • Jr. Member
  • *****
  • Posts: 57
  • Ritesh Shah
    • View Profile
    • IT Technology Blog
OUTPUT Clause in SQL Server
« 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/

Code: [Select]
http://blog.extreme-advice.com/2009/08/17/output-clause-in-sql-server-20082005/
Ritesh Shah
Administrator
Extreme-Advice.com
SQLHub.com