一、引言

A表数据同步至B表的场景很常见，比如一个公司有总部及分厂，它们使用相同的系统，只是账套不同。此时，一些基础数据如物料信息，只需要总部录入即可，然后间隔一定时间同步至分厂，避免了重复工作。

二、测试数据

CREATE TABLE StudentA(    ID VARCHAR(32),    Name VARCHAR(20),    Sex VARCHAR(10))GOINSERT INTO StudentA (ID,Name,Sex)SELECT '1001','张三','男'UNIONSELECT '1002','李四','男'UNIONSELECT '1003','王五','女'GOCREATE TABLE StudentB(    ID VARCHAR(32),    Name VARCHAR(20),    Sex VARCHAR(10))GOINSERT INTO StudentB (ID,Name,Sex)SELECT '1001','张三','女'UNIONSELECT '1002','李四','女'UNIONSELECT '1003','王五','女'UNIONSELECT '1004','赵六','女'

TRUNCATE TABLE dbo.StudentBINSERT INTO dbo.StudentB SELECT * FROM dbo.StudentA

DELETE FROM dbo.StudentB WHERE NOT EXISTS (SELECT 1 FROM dbo.StudentA WHERE ID=dbo.StudentB.ID)UPDATE B SET B.Name=A.Name,B.Sex=A.SexFROM dbo.StudentA A INNER JOIN dbo.StudentB B ON A.ID=B.IDWHERE CHECKSUM(A.Name,A.Sex)<>CHECKSUM(B.Name,B.Sex)INSERT INTO dbo.StudentB SELECT * FROM dbo.StudentA WHERE NOT EXISTS (SELECT 1 FROM dbo.StudentB WHERE ID=dbo.StudentA.ID)

MERGE INTO dbo.StudentB AS T USING dbo.StudentA AS S ON T.ID=S.IDWHEN MATCHED THEN                --当ON条件成立时，更新数据。    UPDATE SET T.Name=S.Name,T.Sex=S.SexWHEN NOT MATCHED THEN            --当源表数据不存在于目标表时，插入数据。    INSERT VALUES (S.ID,S.Name,S.Sex)WHEN NOT MATCHED BY SOURCE THEN  --当目标表数据不存在于源表时，删除数据。    DELETE;