Granada Coder, C#, Sql Server Blog

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
 

This is an oldie but a goodie.

If you're doing CURSOR development for Sql Server to solve business problems, then STOP.

DOWNLOAD this file (right-click and save-as works best) for an example of non cursor based logic solving.

03 and 04 solve the same business problem, but 2 different ways. 

03 is the cursor model.  04 is the udf model.

Read the comments, and try to make a decent sized database (row count) so you see the massive and significant time difference.

 

If you're using the MembershipProvider (actually the concrete SqlMembershipProvider), then you may have run into an issue where you develop all the Users and Roles on your local machine, using the asp_net.mdf in the Express version of Sql Server 2005.

All of the sudden, you need to deploy your application and you need a different database to store your information.
You'll probably find this resource for deploying on a different version of Sql Server
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

Ok.  You run the aspnet_regsql.exe utility, and you have the correct database, but it is empty.

What do you do now?

I've created this helper script to enable you to move all your applications/users/roles to a new database.
This script doesn't actually INSERT the new data, but creates the TSQL code that you can run on the new database.

I've posted the code here (as a text file).

Hopefully, it will make your life a little easier when you need to copy to a new database all the Membership information.