Output clauses in a SQL Server 2005 database

The OUTPUT clause in SQL Server 2005 allows you to return data to a table variable from the Modify statement (INSERT, UPDATE, delete). Useful scenarios for DML with results include cleanup and archiving, message processing applications, and other scenarios. The syntax for this new OUTPUT clause is:

OUTPUT into @table_variable

You can access the old/new image of the modified row by referencing the inserted table and the deleted table in the same way as the access trigger. In an INSERT statement, you can access only the inserted table. In the DELETE statement, only the deleted table can be accessed. In the UPDATE statement, you can access the inserted tables and the deleted tables. Here are examples of MSDN:

As an example of a cleanup and archiving scenario that might be useful as a result of DML, assume that you have a large Orders table and that you want to purge historical data periodically. You also want to copy the purged data to an archive table named Ordersarchive. You declare a table variable called @deletedOrders, and enter a loop in which you use the Purge method described in the "Top Enhancements" section above to delete historical data (for example, orders earlier than 2003). The added code here is an OUTPUT clause that copies all the properties of all the deleted rows to the @deletedOrders table variable, and then uses the INSERT INTO statement to copy all the rows from the table variable to the ordersarchive table:

DECLARE @deletedOrders TABLE
(
orderid INT,
orderdate DATETIME,
empid INT,
custid VARchar(5),
qty INT
)
WHILE 1=1
BEGIN
BEGIN TRAN
delete TOP (5000) FROM Orders
OUTPUT deleted.* INTO @deletedOrders
where orderdate < ’20030101’
insert INTO OrdersArchive
select * FROM @deletedOrders
COMMIT TRAN
delete FROM @deletedOrders
IF @@rowcount < 5000
BREAK
END