容易遗忘的一些小代码之 Merge Operation and Output Clause

根据与源表, 对目标表执行插入、更新或删除操作.根据在另一个表中找到的差异在一个表中插入,更新或删除行,可以对两个表进行同步. 
在数据仓库应用中,这种SQL语句的使用比SSIS工具的使用更加容易维护些,因为表同步的逻辑可以写在存储过程中,维护的时候只需要维护存储过程即可,
而不需要打开Package去检查SSIS Component的配置.
 
/** 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
**/