从MSSQL向DB2表插入数据的非SSIS高效解决方案咨询

针对你300万行单列数据从MSSQL到DB2的慢插入问题，以下是几个比TSQL直接插入高效得多的方案：

1. BCP + DB2 LOAD/IMPORT工具（推荐） #

这是批量数据迁移的经典高效组合，利用双方原生工具避开跨服务器的小批量传输开销：

• MSSQL端导出数据
  用bcp命令将目标表数据导出为平面文件，示例：

  bcp "SELECT your_single_column FROM YourMSSQLTable" queryout "D:\data_export.txt" -S YourMSSQLServer -d YourDatabase -U username -P password -c -r\n
  

  参数说明：-c采用字符格式导出，-r指定行分隔符（单列无需列分隔符），速度远快于TSQL查询导出。
• DB2端加载数据
  用DB2原生LOAD命令（直接写入存储层，不触发全量日志和约束检查，大数据量下比IMPORT快数倍）：

  LOAD CLIENT FROM 'D:\data_export.txt' OF DEL INSERT INTO YourDB2Table;
  

  若需保留完整日志或触发约束，改用IMPORT：

  IMPORT FROM 'D:\data_export.txt' OF DEL INSERT INTO YourDB2Table;
  

  该方案速度接近SSIS，因为都是基于块级的批量操作。

2. DB2外部表读取MSSQL导出文件 #

让DB2主动读取MSSQL导出的文件，避免跨服务器传输的开销：

• 先用bcp将数据导出到DB2服务器可访问的路径（或共享目录）
• 在DB2中创建外部表指向该文件：

  CREATE EXTERNAL TABLE ext_your_table (single_col VARCHAR(MAX))
  USING (
    FILE '/path/to/data_export.txt'
    FORMAT DEL
    LINEEND '\n'
  );
  

• 直接插入目标表：

  INSERT INTO YourDB2Table SELECT single_col FROM ext_your_table;
  

  这种方式利用DB2的批量读取优化，比TSQL跨服务器插入高效很多。

3. 优化TSQL链接服务器插入（仅当必须用TSQL时） #

如果只能依赖TSQL，可通过以下方式大幅提升速度：

• 分批大批次提交：默认链接服务器的批量提交极小，按10万行/批次拆分插入，示例（假设MSSQL表有自增ID列）：

  DECLARE @BatchSize INT = 100000;
  DECLARE @RowCount INT = 1;
  DECLARE @LastID INT = 0;
  
  WHILE @RowCount > 0
  BEGIN
    INSERT OPENQUERY(YourDB2LinkedServer, 'SELECT single_col FROM YourDB2Table')
    SELECT single_col
    FROM YourMSSQLTable
    WHERE ID > @LastID
    ORDER BY ID
    OFFSET 0 ROWS FETCH NEXT @BatchSize ROWS ONLY;
  
    SET @RowCount = @@ROWCOUNT;
    SET @LastID = (SELECT MAX(ID) FROM YourMSSQLTable WHERE ID > @LastID);
    COMMIT;
  END
  

  无自增ID可改用ROW_NUMBER()生成临时序号分批。
• 临时禁用DB2表的约束和索引：插入前禁用非主键索引、外键约束，插入完成后重建/启用，减少插入时的写开销。
• 添加查询重编译提示：在INSERT OPENQUERY语句末尾加OPTION (RECOMPILE)，让SQL Server生成更优的跨服务器执行计划。

为什么TSQL直接插入慢？ #

链接服务器、OPENQUERY这类方法本质是通过ODBC/OLEDB逐行或极小批量传输数据，每一行都要经过网络协议交互、日志记录，300万行的累积开销极大。而SSIS、BCP+DB2 LOAD都是基于块级批量传输，能把网络和日志开销降到最低。

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