SQL SERVER 数据同步

1、清除表格数据

TRUNCATE TABLE "表格名";

示例：TRUNCATE TABLE dbo.LocalTable

2、插入数据（初始化）

INSERT INTO 目标表 SELECT * FROM 数据源

示例：INSERT INTO dbo.LocalTable SELECT * FROM dbo.SourceTable

3、同步删除

DELETE FROM 目标表 WHERE NOT EXISTS (SELECT 1 FROM 数据源 WHERE 数据源.ID=目标表.ID)

示例：DELETE FROM dbo.LocalTable WHERE NOT EXISTS (SELECT 1 FROM dbo.SourceTable WHERE ID=dbo.LocalTable.ID)

4、同步更新

UPDATE B SET 更新字段
FROM 数据源 A INNER JOIN 目标表 B ON A.ID=B.ID
WHERE CHECKSUM(A.字段)<>CHECKSUM(B.字段)

示例：UPDATE B SET B.Name=A.Name FROM dbo.SourceTable A INNER JOIN dbo.LocalTable B ON A.ID=B.ID WHERE CHECKSUM(A.Name)<>CHECKSUM(B.Name)

5、同步插入

INSERT INTO 目标表 SELECT * FROM 数据源 WHERE NOT EXISTS (SELECT 1 FROM 目标表 WHERE 目标表.ID=数据源.ID)

示例：INSERT INTO dbo.LocalTable SELECT * FROM dbo.SourceTable WHERE NOT EXISTS (SELECT 1 FROM dbo.LocalTable WHERE ID=dbo.SourceTable.ID)

 6、MERGE INTO

ON T.ID=S.ID
WHEN MATCHED --目标表和源表中都有的id,则把源表的数据更新到目标表中去
THEN UPDATE SET T.[DESC]=S.[DESC]
WHEN NOT MATCHED --目标表中没有的id，源表中有，则把源表中的数据插入到目标表中
THEN INSERT VALUES(S.ID,S.[DESC])
WHEN NOT MATCHED BY SOURCE--目标表中有的id,源表中没有，则把目标表中的id对应的记录删掉
THEN DELETE;

 示例：

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