0 OUTPUT & MERGE

OUTPUT is used to retrieved the modified data. $actions indicate what action is been taken ( UPDATE, INSERT,DELETE).

MERGE is used to combine the data from different tables.

Create the test data

use [TestDB]

GO

create table dbo.source (

    EmployeeID int,

    EmpolyeeName varchar(20),

    CONSTRAINT source_PK Primary KEY ( EmployeeID)

)

GO

create table dbo.Target (

    EmployeeID int,

    EmpolyeeName varchar(20),

    CONSTRAINT target_PK Primary KEY ( EmployeeID)

)

GO

INSERT INTO dbo.Target( EmployeeID, EmpolyeeName) 

VALUES( 100,'Summery'),(101,'Sunny'),(102,'Skye')

GO

INSERT INTO dbo.source( EmployeeID, EmpolyeeName) 

VALUES( 102,'PO'),(103,'Eva'),(104,'Panda')

GO

Test the MERGE and OUTPUT

MERGE dbo.Target AS T

USING dbo.source AS S

    ON T.EmployeeID = S.EmployeeID

WHEN MATCHED THEN 

    UPDATE SET T.EmpolyeeName = S.EmpolyeeName

WHEN NOT MATCHED THEN

    INSERT VALUES ( S.EmployeeID, S.EmpolyeeName)

OUTPUT $action AS 'ACTION',

    INSERTED.EmployeeID AS 'New Employee ID',

    INSERTED.EmpolyeeName AS 'New Employee Name',

    DELETED.EmployeeID AS 'Old Employee ID',

    DELETED.EmpolyeeName AS 'Old Employee Name';

After the merge