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;
Sql Merge实现数据同步
最新推荐文章于 2020-11-22 15:53:57 发布