Between work and diaper changes I've been reading Michael Coles's book Pro T-SQL 2008 Programmer's Guide, and found this little gem (pg. 527-528):
The OUTPUT Clause
You can use the OUTPUT clause with the the INSERT, UPDATE, DELETE and MERGE DML statements. ... The OUTPUT clause returns information about the rows affected by the DML statements that can be useful in comparing preupdate and postupdate data, or for troubleshooting and logging purposes. ... You can use the OUTPUT clause to output a SQL result set like that returned by a SELECT statement, or you can combine OUTPUT with the INTO clause to output rows to a table or a table variable.
This feature is supported in T-SQL 2008, but was initially added to Microsoft SQL Server 2005. And here it is three years later and I'm just learning about it!
One use of the OUTPUT clause is to grab the just-inserted IDENTITY column value:
INSERT INTO TableName(ColumnList)
OUTPUT inserted.IdentityColumnName
VALUES(Values)
The above will return the just-inserted IDENTITY value as a result set, just as if you had followed an OUTPUT-less INSERT statement with the statement:
SELECT SCOPE_IDENTITY()
You could use the OUTPUT statement to return information about the rows affected by an UPDATE. For example, in these tough economic times you might need to increase prices by 20% for all products that cost less than $10.00. The following statement performs the described update and returns those products whose prices were increased, showing both their old price and their new price:
UPDATE Products SET
Price = Price * 1.20
OUTPUT inserted.ProductID, deleted.Price AS OldPrice, inserted.Price AS NewPrice
WHERE Price < 10.00
This UPDATE statement will modify the data and return a three-column result set with a row for each modified product along with its preupdate price (deleted.Price) and its post-update price (inserted.Price).
Pretty neat, eh?
In fact, You can string these DML statements together, so you do an UPDATE with an OUTPUT whose results are then automatically INSERTed into another table (such as an audit table). The OUTPUT statement feels like in-place triggers (kind of like how Common Table Expressions are akin to in-place views).
I'll have to write an article on the OUTPUT statement on 4Guys one of these days...
Further Reading....