Merge数据同步

--会话窗口55
CREATE TABLE TUser_back0501
(
    FID INT,
    FRemark NVARCHAR(30)
)

CREATE TABLE TUser
(
    FID INT,
    FRemark NVARCHAR(30)
)

GO

 

--会话窗口56
TRUNCATE TABLE TUser_back0501
TRUNCATE TABLE TUser
GO


--备份表(源表)
INSERT INTO TUser_back0501(FID,FRemark) VALUES (1,N'back0501备注--源表')
INSERT INTO TUser_back0501(FID,FRemark) VALUES (2,N'back0501备注--源表')
INSERT INTO TUser_back0501(FID,FRemark) VALUES (3,N'back0501备注--源表')
INSERT INTO TUser_back0501(FID,FRemark) VALUES (4,N'back0501备注--源表')

GO


--正式表(目标表)
INSERT INTO TUser(FID,FRemark) VALUES (1,N'TUser备注--目标表')
INSERT INTO TUser(FID,FRemark) VALUES (2,N'TUser备注--目标表')
INSERT INTO TUser(FID,FRemark) VALUES (5,N'TUser备注--目标表')
INSERT INTO TUser(FID,FRemark) VALUES (6,N'TUser备注--目标表')
INSERT INTO TUser(FID,FRemark) VALUES (7,N'TUser备注--目标表')

GO

 

--会话窗口57
SELECT * FROM TUser_back0501
SELECT * FROM TUser 


SELECT a.FID,a.FRemark,'INNER JOIN',b.FID,b.FRemark
FROM TUser AS a
INNER JOIN TUser_back0501 AS b ON b.FID=a.FID

SELECT a.FID,a.FRemark,'LEFT JOIN',b.FID,b.FRemark
FROM TUser AS a
LEFT JOIN TUser_back0501 AS b ON b.FID=a.FID

SELECT a.FID,a.FRemark,'RIGHT JOIN',b.FID,b.FRemark
FROM TUser AS a
RIGHT JOIN TUser_back0501 AS b ON b.FID=a.FID

GO

 

 

使用 MERGE的场景
1.数据同步（在另一个表[源表]中找到的差异数据在一个表[目标表]中插入、更新或删除行）
2.基于源表对目标表做 INSERT、UPDATE、DELETE 操作。

 

--会话窗口60

/*
**目标表(TUser)跟源表(TUser_back0501)对比
**目标表在源表(没有的)就增加
**目标表在源表(是有的)就更新
*/

MERGE INTO TUser AS t         --目标表(TUser)
USING TUser_back0501  AS s    --源表(TUser_back0501)
ON t.FID=s.FID


--当目标表(TUser)和源表(TUser_back0501)匹配时,就更新目标表(TUser)的字段[FRemark]
WHEN MATCHED 
THEN UPDATE SET t.FRemark = N'更新来自-源表(TUser_back0501) -> '+s.FRemark 

--当目标表(TUser)和源表(TUser_back0501)不匹配时,就新增数据到目标表(TUser),新增数据来自源表(TUser_back0501)
WHEN NOT MATCHED 
THEN INSERT VALUES(s.FID,N'新增来自-源表(TUser_back0501) -> '+s.FRemark)
;

GO


SELECT a.FID,a.FRemark
FROM TUser AS a

SELECT a.FID,a.FRemark,'INNER JOIN',b.FID,b.FRemark
FROM TUser AS a
INNER JOIN TUser_back0501 AS b ON b.FID=a.FID

GO

 

--会话窗口61

/*
**目标表(TUser)跟源表(TUser_back0501)对比
**目标表在源表(是有的)就更新
**目标表在源表(没有的)就删除
*/

MERGE INTO TUser AS t         --目标表(TUser)
USING TUser_back0501  AS s    --源表(TUser_back0501)
ON t.FID=s.FID


--当目标表(TUser)和源表(TUser_back0501)匹配时,就更新目标表(TUser)的字段[FRemark]
WHEN MATCHED 
THEN UPDATE SET t.FRemark = N'更新来自-源表(TUser_back0501) -> '+s.FRemark 

/*
--当目标表(TUser)和源表(TUser_back0501)不匹配时,就新增数据到目标表(TUser),新增数据来自源表(TUser_back0501)
WHEN NOT MATCHED 
THEN INSERT VALUES(s.FID,N'新增来自-源表(TUser_back0501) -> '+s.FRemark)
*/


--当目标表(TUser)和源表(TUser_back0501)不匹配时,就删除目标表(TUser)不匹配的数据
WHEN NOT MATCHED BY SOURCE 
THEN DELETE 
;

GO


SELECT a.FID,a.FRemark
FROM TUser AS a

SELECT a.FID,a.FRemark,'INNER JOIN',b.FID,b.FRemark
FROM TUser AS a
INNER JOIN TUser_back0501 AS b ON b.FID=a.FID

GO

 

仅仅是MATCHED这种限制条件往往不能满足实际需求，
我们可以在语句的基础上加上AND附加上额外的限制条件。

--会话窗口62

MERGE INTO TUser AS t         --目标表(TUser)
USING TUser_back0501  AS s    --源表(TUser_back0501)
ON t.FID=s.FID


--当目标表(TUser)和源表(TUser_back0501)匹配时,就更新目标表(TUser)的字段[FRemark]
WHEN MATCHED AND t.FID=1         /*在MATCHED的基础上 附加 额外的限制条件AND */
THEN UPDATE SET t.FRemark = N'更新来自-源表(TUser_back0501) -> '+s.FRemark 

--当目标表(TUser)和源表(TUser_back0501)不匹配时,就新增数据到目标表(TUser),新增数据来自源表(TUser_back0501)
WHEN NOT MATCHED AND s.FID=4     /* 在MATCHED的基础上 附加 额外的限制条件AND */
THEN INSERT VALUES(s.FID,N'新增来自-源表(TUser_back0501) -> '+s.FRemark)
;

GO


SELECT a.FID,a.FRemark
FROM TUser AS a

SELECT a.FID,a.FRemark,'INNER JOIN',b.FID,b.FRemark
FROM TUser AS a
INNER JOIN TUser_back0501 AS b ON b.FID=a.FID

GO