根据与源表, 对目标表执行插入、更新或删除操作.根据在另一个表中找到的差异在一个表中插入,更新或删除行,可以对两个表进行同步.
在数据仓库应用中,这种SQL语句的使用比SSIS工具的使用更加容易维护些,因为表同步的逻辑可以写在存储过程中,维护的时候只需要维护存储过程即可,
而不需要打开Package去检查SSIS Component的配置.
/** Merge Operation and Output Clause**/-- Source tableDECLARE @SourceTable TABLE( ID INT PRIMARY KEY, DSPT VARCHAR(50))-- Target tableDECLARE @TargetTable TABLE( ID INT PRIMARY KEY, DSPT VARCHAR(50)) -- Log tableDECLARE @Log TABLE( ID INT IDENTITY PRIMARY KEY, Operation VARCHAR(20), OldID INT, OldValue VARCHAR(100), NeID INT, NewValue VARCHAR(100)) -- Insert testing dataINSERT 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 tableUSING @SourceTable AS S -- Using source table ON T.ID = S.ID -- Join conditions-- If join condition is true, then matchedWHEN MATCHED -- Update or Delete operation THEN UPDATE SET T.DSPT = S.DSPT -- Not matchedWHEN 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 clauseOUTPUT $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 changesSELECT *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 operationINSERT INTO @SourceTable-- To record the inserted ID and Description-- when new record added into @SourcetableOUTPUT 'INSERT',NULL,NULL,Inserted.ID,Inserted.DSPT INTO @LogVALUES(10,'Insert a new value') -- Show the final resultSELECT *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
**/
