SQL Server批量插入大量数据优化及纯SQL实现咨询

一、Python端批量插入优化 #

逐行插入慢的核心原因是单条语句的网络往返和执行开销太高，换成批量提交能大幅提速：

• 使用executemany批量提交：把所有待插入数据整理成二维列表（比如[(a1,b1,c1,d1), (a2,b2,c2,d2)...]），一次性传给executemany，驱动会打包批量发送请求，比循环逐行快数倍。
• 开启fast_executemany（pyodbc专属）：pyodbc默认关闭了这个优化，开启后会把参数批量推送给SQL Server，进一步降低网络开销。只需在执行前加cursor.fast_executemany = True，再配合executemany使用。
• 合并多行INSERT语句：把多条VALUES子句合并成一个INSERT语句，比如INSERT INTO table1 (colA,colB,colC,colD) VALUES (v1a,v1b,v1c,v1d), (v2a,v2b,v2c,v2d), ...，注意单条语句长度别超SQL Server限制，分批次执行即可。

二、脱离Python，纯SQL Server完成插入 #

如果不需要Python介入，根据数据来源选择对应方法：

• BULK INSERT导入本地/共享文件：针对CSV、TXT等结构化文件，直接用命令导入：

  BULK INSERT table1
  FROM '\\server\share\data.csv'
  WITH (
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      FIRSTROW = 2, -- 跳过表头行
      TABLOCK -- 启用表锁提升导入速度
  );
  

• OPENROWSET(BULK...)带转换导入：如果需要先清洗数据再插入，可先用OPENROWSET读取文件到临时结果集，处理后再插入目标表：

  INSERT INTO table1 (colA, colB, colC, colD)
  SELECT 
      TRIM(colA), -- 示例：清洗字段
      CAST(colB AS INT), -- 类型转换
      colC,
      colD
  FROM OPENROWSET(
      BULK 'C:\local_data.csv',
      FORMAT = 'CSV',
      FIRSTROW = 2
  ) AS input_data;
  

• INSERT...SELECT从其他表复制数据：如果数据已经在SQL Server的其他表中，直接用SELECT批量插入：

  INSERT INTO table1 (colA, colB, colC, colD)
  SELECT src_colA, src_colB, src_colC, src_colD
  FROM source_table
  WHERE create_time >= '2024-01-01'; -- 可选过滤条件
  

• SSIS可视化导入：如果是周期性的批量导入需求，用SQL Server Integration Services（SSIS）创建导入包，可视化配置数据源、转换规则和目标表，适合复杂ETL场景，效率拉满。

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