Microsoft® SQL Server 2012 Unleashed

Insert over DML

In SQL Server 2008, the OUTPUT clause was expanded to allow it to be used within Data Manipulation Language (DML) statements within the context of an INSERT...SELECT statement.

For example, assume you are running a MERGE statement and you want the output of this to be put into a separate audit or processing table. This can be done by executing the MERGE statement with an OUTPUT clause, which is incorporated as a derived table in the SELECT clause of an INSERT statement.

To demonstrate this capability, create the following table for storing the data:

Click here to view code image

if OBJECT_ID('inventory_audit') is not null    drop table inventory_auditgoCREATE TABLE inventory_audit( Action     varchar(10) not null, Src_ProductID     varchar(6) ...