Microsoft® SQL Server 2008 R2 Unleashed

The OUTPUT Clause

By default, the execution of a DML statement such as INSERT, UPDATE, or DELETE does not produce any results that indicate what rows changed except for checking @@ROWCOUNT to determine the number of rows affected.

In SQL Server 2005, the INSERT, UPDATE, and DELETE statements were enhanced to support an OUTPUT clause to be able to identify the actual rows affected by the DML statement. The OUTPUT clause allows you to return data from a modification statement (INSERT, UPDATE, or DELETE). This data can be returned as a result set to the caller or returned into a table variable or an output table. To capture information on the affected rows, the OUTPUT clause provides access to the inserted and deleted virtual tables that are normally ...