The OUTPUT Clause
SQL Server 2005 introduces the OUTPUT clause to capture the data rows changed when a DML statementthat is, INSERT, UPDATE, or DELETEis executed. The OUTPUT clause can be specified with any of the DML operations; you can use the inserted and deleted virtual tables with the OUTPUT statement and copy the affected rows into a table variable.
Here is an example of using the OUTPUT clause to capture the rows updated:
USE AdventureWorks; GO BEGIN TRANSACTION; DECLARE @varOriginalRows AS TABLE ( CustomerID INT, Name NVARCHAR(50), SalesPersonID INT, Demographics XML, rowguid uniqueidentifier, ModifiedDate datetime ); UPDATE Sales.Store SET SalesPersonID = 280 OUTPUT deleted.* INTO @varOriginalRows WHERE SalesPersonID = 282; SELECT * FROM @varOriginalRows; ROLLBACK TRANSACTION; GO
The preceding script declares a table variable that has the same schema as Sales.Store because you want to capture all the columns with the OUTPUT clause. The UPDATE DML statement uses the OUTPUT clause to capture all the rows that are affected and saves them into the @varOriginalRows table variable. The script is executed in a transaction scope, which is rolled back toward the end to avoid making any changes to the table.