Moving data between source and destination tables using the OUTPUT clause in SQL Server 2005+

Posted by Dan on Aug 19, 2011 @ 8:00 AM

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!

Categories: SQL, HTML/ColdFusion

Add Comment

Leave this field empty