Tuesday, September 30, 2014

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

image

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';

image

After the merge

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates