如何高效实现Production表向Production_History表的更新与新增？

针对你15万行表的同步需求，以下是几个能大幅提升效率的方案，同时解决内连接更新的语法问题：

1. 先给唯一键加高效索引 #

FARM_ID+COLLECTION_DATE是同步的核心标识，必须在两张表上都创建合适的索引，这是所有优化的基础：

• 在Production表创建联合非聚集索引，加速匹配查询：

  CREATE NONCLUSTERED INDEX IX_Production_FarmCollection ON Production (FARM_ID, COLLECTION_DATE);
  

• 把Production_History的FARM_ID+COLLECTION_DATE设为聚集主键（如果还没设置），聚集索引的查找和更新效率远高于非聚集：

  ALTER TABLE Production_History ADD CONSTRAINT PK_ProductionHistory_FarmCollection PRIMARY KEY CLUSTERED (FARM_ID, COLLECTION_DATE);
  

2. 用MERGE语句一站式完成新增+更新 #

MERGE是SQL Server专为同步场景设计的语句，只需要扫描源表和目标表各一次，比分开执行INSERT和UPDATE效率高很多，语法如下：

MERGE INTO Production_History AS Target
USING Production AS Source
ON Target.FARM_ID = Source.FARM_ID AND Target.COLLECTION_DATE = Source.COLLECTION_DATE
WHEN MATCHED THEN
    UPDATE SET
        Target.Column1 = Source.Column1,
        Target.Column2 = Source.Column2,
        -- 依次列出需要更新的15列，不要用SELECT *避免意外
        Target.Column15 = Source.Column15
WHEN NOT MATCHED BY Target THEN
    INSERT (FARM_ID, COLLECTION_DATE, Column1, Column2, ..., Column15)
    VALUES (Source.FARM_ID, Source.COLLECTION_DATE, Source.Column1, Source.Column2, ..., Source.Column15);

如果SSIS是增量更新，建议加过滤条件（比如Production表有Last_Updated列），只同步最近修改的数据，避免全表扫描：

-- 在USING子句里加过滤
USING (SELECT * FROM Production WHERE Last_Updated >= DATEADD(HOUR, -2, GETDATE())) AS Source

3. 分批处理大更新避免锁阻塞 #

如果单次更新的数据量过大（比如上万行），一次性执行会长时间锁表，导致效率低下甚至阻塞其他操作。可以分批处理，每次更新1000-5000行：

DECLARE @BatchSize INT = 1000;
DECLARE @RowCount INT = 1;

WHILE @RowCount > 0
BEGIN
    BEGIN TRANSACTION;

    -- 只更新匹配的行，分批处理
    MERGE TOP (@BatchSize) INTO Production_History AS Target
    USING (
        SELECT TOP (@BatchSize) p.* 
        FROM Production p
        INNER JOIN Production_History ph 
            ON p.FARM_ID = ph.FARM_ID AND p.COLLECTION_DATE = ph.COLLECTION_DATE
        -- 同样可以加时间过滤，只处理最新数据
        WHERE p.Last_Updated >= DATEADD(HOUR, -2, GETDATE())
    ) AS Source
    ON Target.FARM_ID = Source.FARM_ID AND Target.COLLECTION_DATE = Source.COLLECTION_DATE
    WHEN MATCHED THEN
        UPDATE SET
            Target.Column1 = Source.Column1,
            -- 其他14列
            Target.Column15 = Source.Column15;

    SET @RowCount = @@ROWCOUNT;
    COMMIT TRANSACTION;
    WAITFOR DELAY '00:00:01'; -- 可选，给数据库释放锁的时间
END

4. 修复内连接更新的语法错误 #

你之前内连接更新出错，大概率是语法问题，正确的内连接更新写法如下：

UPDATE ph
SET
    ph.Column1 = p.Column1,
    ph.Column2 = p.Column2,
    -- 列出所有需要更新的列
    ph.Column15 = p.Column15
FROM Production_History ph
INNER JOIN Production p
    ON ph.FARM_ID = p.FARM_ID AND ph.COLLECTION_DATE = p.COLLECTION_DATE
-- 可选：只更新最近修改的行
WHERE p.Last_Updated >= DATEADD(HOUR, -2, GETDATE());

如果还是报错，检查：

• 列名是否拼写正确，同名列必须加表别名区分
• 有没有触发器或外键约束阻塞更新（可以临时禁用非必要约束，同步后再启用）
• 数据库锁冲突：用SELECT * FROM sys.dm_tran_locks查看当前锁情况

5. 额外优化点 #

• 更新统计信息：过时的统计信息会导致数据库生成低效的执行计划，执行以下语句更新：

  UPDATE STATISTICS Production;
  UPDATE STATISTICS Production_History;
  

• 禁用非必要触发器：如果Production_History有同步相关的触发器，临时禁用可以大幅提升更新速度
• 确认SSIS的更新逻辑：如果SSIS是按批次更新，尽量获取批次标识，只同步对应批次的数据，避免全表扫描

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