Sql Merge实现数据同步

USE Test
GO
--原数据表
CREATE TABLE StudentSource
(
	StudentGUID UNIQUEIDENTIFIER,
	StudentNO VARCHAR(100),
	StudentName NVARCHAR(200),
	StudentSex VARCHAR(1)
)
--目标数据表
CREATE TABLE StudentTarget
(
	StudentGUID UNIQUEIDENTIFIER,
	StudentNO VARCHAR(100),
	StudentName NVARCHAR(20)
)

SELECT NEWID()
--原数据库数据
INSERT INTO StudentSource(StudentGUID,StudentNO,StudentName,StudentSex)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC1','S003','张三','1');
INSERT INTO StudentSource(StudentGUID,StudentNO,StudentName,StudentSex)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC2','S004','李四','1');
INSERT INTO StudentSource(StudentGUID,StudentNO,StudentName,StudentSex)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC3','S005','王五','1');
INSERT INTO StudentSource(StudentGUID,StudentNO,StudentName,StudentSex)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC4','S006','马六','1');
--原数据库数据
INSERT INTO StudentTarget(StudentGUID,StudentNO,StudentName)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC2','S0041','李四1');
INSERT INTO StudentTarget(StudentGUID,StudentNO,StudentName)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC3','S0051','王五1');
INSERT INTO StudentTarget(StudentGUID,StudentNO,StudentName)VALUES('95EB0D9D-8F0F-4796-9E29-343EAB774CC5','S007','马七');


--DROP TABLE StudentSource
--DROP TABLE StudentTarget
--DELETE FROM StudentSource
--DELETE FROM StudentTarget

SELECT * FROM StudentSource ORDER BY StudentNO ASC
SELECT * FROM StudentTarget ORDER BY StudentNO ASC

MERGE INTO StudentTarget AS T
USING StudentSource AS S
ON T.StudentGUID=S.StudentGUID
WHEN MATCHED--源数据、目标数据同时存在，更新
THEN UPDATE SET T.StudentNO=S.StudentNO,T.StudentName=S.StudentName
WHEN NOT MATCHED--目标数据不存在，插入
THEN INSERT VALUES(S.StudentGUID,S.StudentNO,S.StudentName)
WHEN NOT MATCHED BY SOURCE--源数据不存在，删除
THEN DELETE;