One of the features introduced in Microsoft SQL 2005 that I think really goes largely unused in the "OUTPUT" clause. It certainly was a feature that went unnoticed by me for a long time and even once I became aware of the feature, didn't really put it to much use. However, lately I've been refactored a ton of old SQL into objects and with this refactoring I've been making high use of the OUTPUT clause in order to help create audit trails. It's been a great way to know which rows were affected in your CRUD operations.
There's really a ton of useful things you can do with the OUTPUT clause, but one thing it allows you to do is to migrate data from one table to another in a single atomic operation.
Let's look at the syntax:
-- the table to move data from delete dbo.SourceTable -- get the data removed from the "source" table output deleted.column1, deleted.column2, deleted.column3 -- insert the deleted row into the "destination" table into dbo.DestinationTable -- your where clause for the "delete" operation where Column1 > 2000 and Column2 = 1234
Here's a fully working example that you can run in SSMS:
-- declare temp tables declare @original table (id int identity(1,1), name varchar(20), company varchar(20), dob datetime) declare @new table (id int, name varchar(20), company varchar(20), dob datetime) -- insert some fake data into our "original" table insert into @original (name, company, dob) values ('Bill Gates', 'Microsoft', '1955-10-28') insert into @original (name, company, dob) values ('Paul Allen', 'Microsoft', '1953-01-21') insert into @original (name, company, dob) values ('Steve Jobs', 'Apple', '1955-02-24') insert into @original (name, company, dob) values ('Steve Wozniak', 'Apple', '1950-08-11') -- show the results select 'original' as tableName, * from @original select 'new' as tableName, * from @new /* here is the core SQL used to do the move */ delete @original -- get the data removed from the @original table output deleted.id, deleted.name, deleted.company, deleted.dob -- insert the deleted row into the @new table into @new -- your where clause for the "delete" operation where company = 'Microsoft' -- show the new results select 'original' as tableName, * from @original select 'new' as tableName, * from @new
If you run this example in SSMS, you'll see the two "Microsoft" records move from the @original table into the @new table.
NOTE:The @original and @new tables are simple table variables (which are like temp tables.)
There really is a ton of useful things you can do with the OUTPUT clause, so I highly recommend playing around with the OUTPUT clause if you're using SQL Server 2005 or later!
Comments for this entry have been disabled.