SQL - Merge Operation and Output Clause

/** Merge Operation and Output Clause**/

-- Source table

DECLARE @SourceTable TABLE

(

   ID INT PRIMARY KEY,

   DSPT VARCHAR(50)

)

-- Target table

DECLARE @TargetTable TABLE

(

   ID INT PRIMARY KEY,

   DSPT VARCHAR(50)

)

-- Log table

DECLARE @Log TABLE

(

   ID INT IDENTITY PRIMARY KEY,

   Operation VARCHAR(20),

   OldID INT,

   OldValue VARCHAR(100),

   NeID INT,

   NewValue VARCHAR(100)

)

-- Insert testing data

INSERT INTO @SourceTable VALUES

(1,'ST 1001'),

(2,'ST 1002'),

(3,'ST 1003'),

(4,'ST 1004'),

(5,'ST 1005')

INSERT INTO @TargetTable VALUES

(1,'TT 1001'),

(2,'TT 1002'),

(3,'TT 1003'),

(6,'TT 1006'),

(7,'TT 1007')

SELECT *

FROM @SourceTable

/*****************************************

1    ST 1001

2    ST 1002

3    ST 1003

4    ST 1004

5    ST 1005

*****************************************/

SELECT *

FROM @TargetTable

/*****************************************

1    TT 1001

2    TT 1002

3    TT 1003

6    TT 1006

7    TT 1007

*****************************************/

/** Merge operation **/

MERGE INTO @TargetTable AS T           -- Merge data from source table into target table

USING @SourceTable AS S                -- Using source table

   ON T.ID = S.ID                      -- Join conditions

-- If join condition is true, then matched

WHEN MATCHED        

   -- Update or Delete operation               

   THEN UPDATE SET T.DSPT = S.DSPT 

-- Not matched

WHEN NOT MATCHED BY TARGET  

-- Insert new data       

   THEN INSERT VALUES(S.ID,S.DSPT)

-- Delete or update by using flag to indicate the values in target

-- table don't exist in source table   

WHEN NOT MATCHED BY SOURCE           

   THEN DELETE

-- Can log the operation details by using output clause

OUTPUT $ACTION AS [ACTION],

   Deleted.ID AS 'Deleted ID',

   Deleted.DSPT AS 'Deleted Description',

   Inserted.ID AS 'Inserted ID',

   Inserted.DSPT AS 'Inserted Description'

INTO @Log;

-- Show the changes

SELECT *

FROM @Log

/****************************************

1    UPDATE    1        TT 1001    1        ST 1001

2    UPDATE    2        TT 1002    2        ST 1002

3    UPDATE    3        TT 1003    3        ST 1003

4    INSERT    NULL    NULL    4        ST 1004

5    INSERT    NULL    NULL    5        ST 1005

6    DELETE    6        TT 1006    NULL    NULL

7    DELETE    7        TT 1007    NULL    NULL

****************************************/

SELECT *

FROM @SourceTable

/****************************************

1    ST 1001

2    ST 1002

3    ST 1003

4    ST 1004

5    ST 1005

****************************************/

SELECT *

FROM @TargetTable

/********************************************

1    ST 1001

2    ST 1002

3    ST 1003

4    ST 1004

5    ST 1005

********************************************/

-- Log user's insert operation

INSERT INTO @SourceTable

-- To record the inserted ID and Description

-- when new record added into @Sourcetable

OUTPUT 'INSERT',NULL,NULL,Inserted.ID,Inserted.DSPT

   INTO @Log

VALUES(10,'Insert a new value')

-- Show the final result

SELECT *

FROM @Log

/********************************************

1    UPDATE    1    TT 1001    1    ST 1001

2    UPDATE    2    TT 1002    2    ST 1002

3    UPDATE    3    TT 1003    3    ST 1003

4    INSERT    NULL    NULL    4    ST 1004

5    INSERT    NULL    NULL    5    ST 1005

6    DELETE    6    TT 1006    NULL    NULL

7    DELETE    7    TT 1007    NULL    NULL

8    INSERT    NULL    NULL    10    Insert a new value

**************************************************/