OUTPUT is an interesting clause added starting Sql Server 2005.
Recently, in my previous post, we saw an example with some insight into the usage of OUTPUT clause with DELETE statement. In this post we’ll cover INSERT, UPDATE and MERGE.
In an UPDATE statement, both INSERTED.* and DELETED.* internal tables are available. They store the before and after snapshot of the affected records.
Alternatively, the resultant records could be stored in a temporary table for further processing.
-- -- Usage example of OUTPUT Clause with UPDATE statement -- UPDATE SomeTable SET Status = 69874 OUTPUT INSERTED.CustomerPK , INSERTED.AdjAmount , DELETED.AveragePoints WHERE TargetID = 1656 AND Status = 0 GO
In INSERT statement, when OUTPUT clause is used, only the INSERTED.* internal table is available. The result of this table could be sent directly back or could be stored in a temporary table or table variable for further processing.
-- -- Usage example of OUTPUT Clause with INSERT statement -- INSERT INTO dbo.SomeTable (CustomerID, AdjAmt, LastUpdate) OUTPUT INSERTED.CustomerID , INSERTED.AdjAmt , INSERTED.LastUpdate INTO @MyTableVariable VALUES (123, 300.0, GETDATE()) SELECT * FROM @MyTableVariable GO
In MERGE statement also, we could use both INSERTED.* and DELETED.* internal tables to retrieve the snapshot of data set before and after the change. One unique aspect to this is the $action that indicates the action that resulted in the record. It says either INSERT or DELETE. Keep in mind, MERGE statement is added since Sql Server 2008.
-- -- Usage example of OUTPUT Clause with MERGE statement -- MERGE SomeTarget AS TGT USING ( -- some source data set ) AS SRC ON TGT.CustomerPK = SRC.CustomerPK WHEN MATCHED THEN UPDATE SET TGT.WaitingAck= 0 WHEN NOT MATCHED THEN INSERT (CustomerPK, AdjAmount, LastUpdate) VALUES (123, 300.0, GETDATE()) OUPUT $action , INSERTED.CustomerPK , INSERTED.AdjAmount , INSERTED.LastUpdate , DELETED.CustomerPK , DELETED.AdjAmount , DELETED.LastUpdate GO
Hope this helps,