数据同步方法

数据同步方法

1、
TRUNCATE TABLE dbo.StudentB

INSERT INTO dbo.StudentB SELECT * FROM dbo.StudentA

2
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.Sex

FROM dbo.StudentA A INNER JOIN dbo.StudentB B ON A.ID=B.ID

WHERE 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)

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