Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
MERGE with OUTPUT
-- MERGE with OUTPUT -- inserted.* returns all the inseted columns create table MyOrders( orderid INT NOT NULL PRIMARY KEY, custid INT NOT NULL, empid INT NOT NULL, orderdate DATE NOT NULL ) MERGE INTO MyOrders AS TGT USING (VALUES(1, 70, 1, '20061218'), (2, 70, 7, '20070429'), (3, 70, 7, '20070820'), (4, 70, 3, '20080114'), (5, 70, 1, '20080226'), (6, 70, 2, '20080410')) AS SRC(orderid, custid, empid, orderdate ) ON SRC.orderid = TGT.orderid WHEN MATCHED AND ( TGT.custid <> SRC.custid OR TGT.empid <> SRC.empid OR TGT.orderdate <> SRC.orderdate) THEN UPDATE SET TGT.custid = SRC.custid, TGT.empid = SRC.empid, TGT.orderdate = SRC.orderdate WHEN NOT MATCHED THEN INSERT VALUES(SRC.orderid, SRC.custid, SRC.empid, SRC.orderdate) WHEN NOT MATCHED BY SOURCE THEN DELETE -- Code for output data in merge statement OUTPUT $action AS the_action, COALESCE(inserted.orderid, deleted.orderid) AS orderid,inserted.*; -- inserted.* returns all the inseted columns
run
|
edit
|
history
|
help
0
Please
log in
to post a comment.
Quiz
Task_3_FInal
Create Index On Viiew
Insert multiple parent-child records
Query through an error in Sql Server 2008 r2
SQL Interview Questions : Customer Orders
Uzd
Task_2_Final
TUAN 7_QLDT
employee table
Please log in to post a comment.