OUTPUT Clause (Transact-SQL)

This Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Here I use the AdventureWorks sample database to show the sample T SQL for OUTPUT clause.

USE AdventureWorks;

BEGIN TRANSACTION;

DECLARE @testoutput TABLE

(

SalesOrderID int

, SalesOrderDetailID int

, QtyBefore int

, QtyAfter int

, ModifiedDate datetime2

, UserName varchar(30)

)

SELECT * FROM @testoutput;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

OUTPUT inserted.SalesOrderID

, inserted.SalesOrderDetailID

, deleted.OrderQty

, inserted.OrderQty

, GETDATE()

, SUSER_NAME()

INTO @testoutput

WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;

SELECT * FROM @testoutput;

ROLLBACK TRANSACTION;

SELECT OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;