如何高效将百万行CSV文件整体导入SQL Server数据库

你当前的逐行插入逻辑慢的核心原因是pyodbc默认开启自动提交，每执行一次INSERT就会触发一次独立事务的提交+网络往返，百万级调用的开销会被无限放大。单事务包裹确实可以大幅降低提交开销，但还有效率更高的实现方案，按导入速度从快到慢排列如下：

方案1：使用SQL Server原生BULK INSERT（速度最快，百万行耗时通常在数秒级） #

这是SQL Server官方提供的批量导入能力，直接在数据库层面读取解析CSV文件，没有应用层到数据库的逐行数据传输开销，是大数据量导入的首选方案。
注意：该方案要求CSV文件存放在SQL Server服务进程有权限访问的路径下。

import pyodbc

conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};' # 建议升级老旧的ODBC Driver 11，新版本对批量操作兼容性更好
                      'SERVER=Server Name;'
                      'Database=Database Name;'
                      'UID=User ID;'
                      'PWD=Password;')
cursor = conn.cursor()

bulk_insert_sql = """
BULK INSERT [Table Name]
FROM 'C:\\local_path_on_sql_server\\your_data.csv' -- 此处填写SQL Server服务可访问的CSV绝对路径
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2, -- 首行是表头则设为2，无表头设为1
    TABLOCK -- 导入时申请表级锁，减少锁开销
)
"""
cursor.execute(bulk_insert_sql)
conn.commit()
cursor.close()
conn.close()

方案2：使用pyodbc的fast_executemany批量插入（无需数据库能直接访问CSV，速度仅次于BULK INSERT） #

开启fast_executemany参数后，pyodbc会把所有待插入的数据打包成批次一次性发送给SQL Server执行，避免逐行网络传输的开销，百万行数据通常几十秒就能完成导入。

import pyodbc
import pandas as pd

df = pd.read_csv("your_local_file.csv")
conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'SERVER=Server Name;'
                      'Database=Database Name;'
                      'UID=User ID;'
                      'PWD=Password;')
cursor = conn.cursor()

# 核心配置：开启快速批量执行
cursor.fast_executemany = True

# 提前把DataFrame转成参数列表，不要用iterrows逐行遍历，减少pandas行遍历开销
insert_params = df[['A', 'B', 'C']].values.tolist()
cursor.executemany(
    "INSERT INTO [Table Name]([A],[B],[C]) VALUES (?,?,?)",
    insert_params
)

conn.commit()
cursor.close()
conn.close()

方案3：单事务包裹逐行插入（仅作参考，速度远低于前两种方案） #

你提到的单事务实现逻辑本质是关闭自动提交，等所有插入语句执行完成后再做一次事务提交，相比你当前的代码能快5~10倍，但还是存在百万次单语句执行的开销，仅适合小批量数据场景。
注意：你原代码的INSERT语句列名末尾多了一个冗余逗号，会触发语法错误，需要删除。

import pyodbc
import pandas as pd

df = pd.read_csv("your_local_file.csv")
# 关闭自动提交，所有操作属于同一个事务
conn = pyodbc.connect('Driver={ODBC Driver 17 for SQL Server};'
                      'SERVER=Server Name;'
                      'Database=Database Name;'
                      'UID=User ID;'
                      'PWD=Password;',
                      autocommit=False)
cursor = conn.cursor()

for _, row in df.iterrows():
    cursor.execute(
        "INSERT INTO [Table Name]([A],[B],[C]) VALUES (?,?,?)",
        row['A'], row['B'], row['C']
    )

# 所有语句执行完成后统一提交
conn.commit()
cursor.close()
conn.close()

优化提示 #

• 导入百万级数据前，可以临时删除目标表的非聚集索引、外键约束，导入完成后再重建，能进一步降低写入开销
• 不建议直接使用pandas默认参数的to_sql方法写入SQL Server，其底层默认逐行插入，性能极差
• ODBC Driver 11版本存在fast_executemany兼容bug，使用批量功能时建议升级到17/18版本

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