多表数据同步优化咨询：替换删表重建，实现无中断增量更新

一、先修正你当前的SQL错误 #

你的SQL有两个明显问题：

1. UPDATE语句中更新主键tt.pnr完全多余（JOIN条件已经保证tt.pnr = t.pnr），且修改主键可能引发约束冲突；
2. INSERT语句中not exsits拼写错误，应为not exists。

修正后的基础SQL：

-- 更新匹配主键的记录
UPDATE transfertable tt
SET tt.surname = t.surname,
    tt.name = t.name
FROM transfertable tt
JOIN [table] t ON tt.pnr = t.pnr;

-- 插入不存在的新记录
INSERT INTO transfertable (pnr, surname, name)
SELECT t.pnr, t.surname, t.name 
FROM [table] t
WHERE NOT EXISTS (
    SELECT 1
    FROM transfertable tt
    WHERE tt.pnr = t.pnr
);

注意：table是SQL关键字，建议给表名加方括号或改名避免语法问题。

二、更优实现：MERGE原子操作 #

分开的UPDATE+INSERT存在并发风险（比如两次操作间隙有其他写入），推荐用MERGE语句将更新+插入合并为原子操作，保证数据一致性，代码更简洁：

MERGE INTO transfertable tt
USING [table] t ON tt.pnr = t.pnr
WHEN MATCHED THEN
    UPDATE SET 
        tt.surname = t.surname,
        tt.name = t.name
WHEN NOT MATCHED THEN
    INSERT (pnr, surname, name)
    VALUES (t.pnr, t.surname, t.name);

MERGE支持大部分主流数据库（SQL Server、Oracle、PostgreSQL 15+、MySQL 8.0+等），一次完成同步逻辑，避免中间状态的并发问题。

三、多表多字段场景的优化方案 #

针对12-13张表、20+字段的场景，直接单表同步会重复造轮子，推荐以下思路：

1. 先聚合源数据到临时表 #

先将所有源服务器/表的数据合并到一个临时表（比如#Temp_SourceData），再用临时表和TransferTable做同步，减少多次跨服务器查询的开销：

-- 创建临时表（根据实际字段调整）
CREATE TABLE #Temp_SourceData (
    PNr INT PRIMARY KEY,
    SurName VARCHAR(50),
    Name VARCHAR(50),
    -- 其他20+字段...
);

-- 从各源表批量插入数据（跨服务器用链接服务器，比如[ServerA].[DB].[Schema].[Table1]）
INSERT INTO #Temp_SourceData
SELECT PNr, SurName, Name, ... FROM [ServerA].[DB].[Schema].[Table1]
UNION ALL
SELECT PNr, SurName, Name, ... FROM [ServerB].[DB].[Schema].[Table2]
-- 其他10+张表...

-- 用MERGE同步到TransferTable
MERGE INTO transfertable tt
USING #Temp_SourceData t ON tt.pnr = t.pnr
WHEN MATCHED THEN
    UPDATE SET 
        tt.surname = t.surname,
        tt.name = t.name,
        -- 其他字段逐一赋值...
WHEN NOT MATCHED THEN
    INSERT (pnr, surname, name, ...)
    VALUES (t.pnr, t.surname, t.name, ...);

-- 清理临时表
DROP TABLE #Temp_SourceData;

2. 字段批量赋值技巧 #

如果字段名完全一致，可以用动态SQL自动生成UPDATE/INSERT的字段列表，避免手动写20+字段：

DECLARE @UpdateFields NVARCHAR(MAX), @InsertFields NVARCHAR(MAX);

-- 生成除主键外的字段列表
SELECT 
    @UpdateFields = STRING_AGG(CONCAT('tt.', COLUMN_NAME, ' = t.', COLUMN_NAME), ', '),
    @InsertFields = STRING_AGG(COLUMN_NAME, ', ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'transfertable' AND COLUMN_NAME != 'PNr';

-- 动态生成MERGE语句
DECLARE @MergeSQL NVARCHAR(MAX) = CONCAT(
    'MERGE INTO transfertable tt
     USING #Temp_SourceData t ON tt.pnr = t.pnr
     WHEN MATCHED THEN
         UPDATE SET ', @UpdateFields, '
     WHEN NOT MATCHED THEN
         INSERT (', @InsertFields, ')
         VALUES (', STRING_AGG(CONCAT('t.', COLUMN_NAME), ', '), ');'
);

EXEC sp_executesql @MergeSQL;

3. 备选方案：双表切换（适合全量更新场景） #

如果源数据变化极大，增量更新逻辑复杂，可以回到全量更新，但用双表切换避免用户报错：

• 准备两张结构完全一致的表：TransferTable（应用访问）和TransferTable_New（临时写入）
• 每次同步时，先清空TransferTable_New，将所有源数据写入新表
• 用原子操作切换表名：

  -- 切换表名（SQL Server示例）
  BEGIN TRANSACTION;
  EXEC sp_rename 'TransferTable', 'TransferTable_Old';
  EXEC sp_rename 'TransferTable_New', 'TransferTable';
  DROP TABLE TransferTable_Old;
  COMMIT;
  

切换过程是原子的，应用几乎不会感知到中断，且无需处理增量逻辑，适合源表数据全量替换的场景。

四、性能与并发注意事项 #

• 确保TransferTable的主键PNr有索引，MERGE/UPDATE/INSERT时能快速匹配记录
• 跨服务器同步时，尽量用批量拉取（避免逐行查询），减少网络开销
• 如果数据量极大，拆分同步批次（比如按PNr范围），避免长时间锁表影响应用

内容的提问来源于stack exchange，提问作者pmbaa