MERGE with OUTPUT, Sql Server

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.

Advertise here.