SQLServer两表数据同步的几种方法

Z时代
2024-01-10
分类：综合

database

一、引言

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

二、测试数据

CREATETABLE StudentA
(
    ID VARCHAR(32),
    Name VARCHAR(20),
    Sex VARCHAR(10)
)
GO
INSERTINTO StudentA (ID,Name,Sex)
SELECT"1001","张三","男"
UNION
SELECT"1002","李四","男"
UNION
SELECT"1003","王五","女"
GO
CREATETABLE StudentB
(
    ID VARCHAR(32),
    Name VARCHAR(20),
    Sex VARCHAR(10)
)
GO
INSERTINTO StudentB (ID,Name,Sex)
SELECT"1001","张三","女"
UNION
SELECT"1002","李四","女"
UNION
SELECT"1003","王五","女"
UNION
SELECT"1004","赵六","女"

三、数据同步方法

3.1、TRUNCATE TABLE

TRUNCATETABLE dbo.StudentB
INSERTINTO dbo.StudentB SELECT*FROM dbo.StudentA

3.2、CHECKSUM

DELETEFROM dbo.StudentB WHERENOTEXISTS (SELECT1FROM dbo.StudentA WHERE ID=dbo.StudentB.ID)
UPDATE B SET B.Name=A.Name,B.Sex=A.Sex
FROM dbo.StudentA A INNERJOIN dbo.StudentB B ON A.ID=B.ID
WHERE CHECKSUM(A.Name,A.Sex)<>CHECKSUM(B.Name,B.Sex)
INSERTINTO dbo.StudentB SELECT*FROM dbo.StudentA WHERENOTEXISTS (SELECT1FROM dbo.StudentB WHERE ID=dbo.StudentA.ID)

3.3、MERGE INTO

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