Microsoft® SQL Server 2008 R2 Unleashed

Insert over DML

Another T-SQL enhancement in SQL Server 2008 applies to the use of the OUTPUT clause. The OUTPUT clause allows you to return data from a modification statement (INSERT, UPDATE, MERGE, or DELETE) as a result set or into a table variable or an output table. In SQL Server 2008, you can include one of these Data Manipulation Language (DML) statements with an OUTPUT clause within the context of an INSERT...SELECT statement.

In the MERGE statement in Listing 42.1, the OUTPUT clause was used to display the rows affected by the statement. Suppose that you want the output of this to be put into a separate audit or processing table. In SQL Server 2008, you can do so by allowing the MERGE statement with the OUTPUT clause to be incorporated ...