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
0 comments:
Post a Comment