SqlServer2005/2008 // OUTPUT clause in INSERT/UPDATE/DELETE statements

SqlServer2005/2008 // OUTPUT clause in INSERT/UPDATE/DELETE statements

These types of samples are all over the place on the web, but here is my original example for which I believe is better clarity.

Original Example(s) at:
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT-clause.aspx

create table PrimaryHolderTable ( i int identity (1001,2) not null primary key, j int not null unique )
create table #OutputResultsHolder ( i int not null, j int not null)
 
insert into PrimaryHolderTable (j)
output inserted.i, inserted.j into #OutputResultsHolder
select top 10 o.object_id from sys.objects as o order by o.object_id desc –<< from sys.objects is there just to provide some rows
 

select * from #OutputResultsHolder
drop table #OutputResultsHolder, PrimaryHolderTable;

go
 
 

create table dbo.EmployeeTable ( EmpKey int identity(1001,2) ,  EmpAge int not null );
create table dbo.AuditTable ( EntityKey int not null default -1  ,  OldValue int null, NewValue int null , Tag varchar(64)  );
 
insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , ‘Employee Inserted’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 values( 18 );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , ‘Employee Inserted’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 values( 20 );

insert into dbo.EmployeeTable (EmpAge)
output inserted.EmpKey , null , inserted.EmpAge , ‘Employee Inserted’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 values( 22 );
 
 
update dbo.EmployeeTable
   set EmpAge  = EmpAge + 1
output inserted.EmpKey , deleted.EmpAge, inserted.EmpAge , ‘Employee Updated’ into dbo.AuditTable ( EntityKey , OldValue , NewValue , Tag)
 where EmpAge <=20;
 
delete from dbo.EmployeeTable
output deleted.EmpKey , deleted.EmpAge, NULL , ‘Employee Deleted’  into dbo.AuditTable (EntityKey , OldValue , NewValue , Tag)
 where EmpAge > 0;–Test multi rows
 
select * from dbo.EmployeeTable;–<<will be empty at this point
select * from dbo.AuditTable;
 
drop table dbo.EmployeeTable, dbo.AuditTable;
go
 

About these ads
This entry was posted in Software Development. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

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