Monday 15 February 2010

OUTPUT clause on an UPDATE statement

Using the OUTPUT clause on an UPDATE statement -

The virtual DELETED table brings back the data values from before the update.
The virtual INSERTED table brings back the data from after the update.

Example :
UPDATE TOP (1) [Import].[ProcessQueue] SET ProcessId = 999
OUTPUT Inserted.QueueId, inserted.QueueName
WHERE QueueBusy = 0

References :
http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx
http://blogs.mssqltips.com/blogs/chadboyd/archive/2007/10/31/katmai-sql-2008-consume-output-from-output-directly.aspx

No comments: