Sql Server OUTPUT Clause Examples with UPDATE INSERT DELETE MERGE
- Posted by Sqltimes
- On December 31, 2014
- 0 Comments
OUTPUT is an interesting clause added starting Sql Server 2005.
DELETE
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.
UPDATE
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.
|
1
2
3
4
5
6
7
8
9
10
11
|
---- Usage example of OUTPUT Clause with UPDATE statement--UPDATE SomeTableSET Status = 69874OUTPUT INSERTED.CustomerPK , INSERTED.AdjAmount , DELETED.AveragePointsWHERE TargetID = 1656AND Status = 0GO |
INSERT
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
---- Usage example of OUTPUT Clause with INSERT statement--INSERT INTO dbo.SomeTable (CustomerID, AdjAmt, LastUpdate)OUTPUT INSERTED.CustomerID , INSERTED.AdjAmt , INSERTED.LastUpdateINTO @MyTableVariableVALUES (123, 300.0, GETDATE())SELECT *FROM @MyTableVariableGO |
INSERT Statement OUTPUT Clause
MERGE
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
|
---- Usage example of OUTPUT Clause with MERGE statement--MERGE SomeTarget AS TGTUSING ( -- some source data set ) AS SRCON TGT.CustomerPK = SRC.CustomerPKWHEN MATCHED THEN UPDATE SET TGT.WaitingAck= 0WHEN 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.LastUpdateGO |
Hope this helps,
_Sqltimes
0 Comments