MERGE-CSDN博客

Merge关键字是一个神奇的DML关键字。它在SQL Server 2008被引入，它能将Insert,Update,Delete简单的并为一句。MSDN对于Merge的解释非常的短小精悍:”根据与源表联接的结果，对目标表执行插入、更新或删除操作。例如，根据在另一个表中找到的差异在一个表中插入、更新或删除行，可以对两个表进行同步。”,通过这个描述，我们可以看出Merge是关于对于两个表之间的数据进行操作的。

可以想象出，需要使用Merge的场景比如:

数据同步
数据转换
基于源表对目标表做Insert,Update,Delete操作



CREATE TABLE TargetTable (
StudentID       NVARCHAR(20),
StudentName     NVARCHAR(20),
StudentScore    INT,
Remark          NVARCHAR(20),
InsertTime      DATETIME,
UpdateTime      DATETIME
)


CREATE TABLE SourceTable (
StudentID       NVARCHAR(20),
StudentName     NVARCHAR(20),
StudentScore    INT,
Remark          NVARCHAR(20),
InsertTime      DATETIME,
UpdateTime      DATETIME
)


INSERT INTO TargetTable SELECT '1001','张三',45,'数学',dateadd(DD,2,GETDATE()),dateadd(hh,8,GETDATE())
INSERT INTO TargetTable SELECT '1002','李四',75,'英语',dateadd(DD,2,GETDATE()),dateadd(hh,8,GETDATE())
--INSERT INTO TargetTable SELECT '1003','王五',95,'化学',dateadd(hh,2,GETDATE()),dateadd(hh,2,GETDATE())

INSERT INTO SourceTable SELECT '1001','张三',77,'数学',dateadd(DD,2,GETDATE()),dateadd(hh,8,GETDATE())
INSERT INTO SourceTable SELECT '1002','李四',75,'英语',dateadd(DD,2,GETDATE()),dateadd(hh,8,GETDATE())
INSERT INTO SourceTable SELECT '1004','赵柳',97,'数学',dateadd(DD,2,GETDATE()),dateadd(hh,8,GETDATE())


SELECT * FROM TargetTable 
SELECT * FROM SourceTable 

;MERGE TargetTable AS tt
USING SourceTable AS st
ON  tt.StudentID = st.StudentID AND tt.Remark = st.Remark
WHEN MATCHED THEN 
UPDATE SET StudentScore = st.StudentScore,UpdateTime = '2016-01-11 17:59:15'
WHEN NOT MATCHED THEN
INSERT (StudentID,StudentName,StudentScore,Remark,InsertTime,UpdateTime) VALUES (StudentID,StudentName,StudentScore,Remark,InsertTime,GETDATE());



SELECT * FROM TargetTable 
SELECT * FROM SourceTable