Pandas to_sql批量插入Synapse失败求助（chunksize>1报错）

Azure Synapse SQL（尤其是无服务器SQL池）不支持SQL Server标准的多值INSERT语法（即VALUES (?, ?, ?), (?, ?, ?)这种批量插入格式），而Pandas的method='multi'参数会生成这种语法，所以当chunksize>1时触发语法错误。

方案1：移除method='multi'参数（简单直接，适合中小数据量） #

保留chunksize参数但去掉method='multi'，Pandas会自动分块执行单条INSERT语句，虽性能不如批量插入，但能直接解决语法问题：

SERVER = "xxx"
DB = "xxx"
USER = "xxx"
PWD = "xxx"

engine = sa.create_engine(f'mssql+pyodbc://{USER}:{PWD}@{SERVER}/{DB}?'
                          'driver=ODBC+Driver+17+for+SQL+Server'
                          '&autocommit=True')

data = {
    'ColumnA': [1, 2, 3, 4, 5],
    'ColumnB': [10, 20, 30, 40, 50],
    'ColumnC': [100, 200, 300, 400, 500],
}

df = pd.DataFrame(data)
# 移除method='multi'，保留chunksize分块
df.to_sql('TESTING', engine, if_exists='append', index=False, chunksize=1000)

方案2：使用bulk_insert_mappings实现批量插入（性能更优） #

借助SQLAlchemy的bulk_insert_mappings方法，绕过Pandasto_sql的批量语法限制，适合中等数据量场景：

from sqlalchemy import Table, MetaData

# 反射目标表结构
metadata = MetaData()
target_table = Table('TESTING', metadata, autoload_with=engine)

# 将DataFrame转为字典列表
data_mappings = df.to_dict('records')

# 执行批量插入
with engine.begin() as conn:
    conn.execute(target_table.insert(), data_mappings)

方案3：使用COPY INTO（大数据量最优解，适配50万行场景） #

对于50万行级别的大表，Synapse官方推荐使用COPY INTO语句，这是性能最高的批量导入方式，步骤如下：

第一步：将DataFrame保存到Azure Data Lake Storage (ADLS) Gen2 #

# 需要先安装azure-storage-file-datalake
from azure.storage.filedatalake import DataLakeServiceClient

# 将DataFrame保存为Parquet格式（比CSV更高效）
df.to_parquet('temp_data.parquet')

# 上传文件到ADLS
service_client = DataLakeServiceClient(account_url="https://<你的存储账户>.dfs.core.windows.net/", credential="<你的存储密钥或令牌>")
file_system_client = service_client.get_file_system_client(file_system="<你的容器名>")
file_client = file_system_client.create_file("temp_data.parquet")

with open('temp_data.parquet', 'rb') as f:
    file_client.upload_data(f, overwrite=True)

第二步：在Synapse SQL中执行导入语句 #

COPY INTO TESTING (ColumnA, ColumnB, ColumnC)
FROM 'https://<你的存储账户>.dfs.core.windows.net/<你的容器名>/temp_data.parquet'
WITH (
    FILE_TYPE = 'PARQUET',
    CREDENTIAL = (IDENTITY = 'Storage Account Key', SECRET = '<你的存储密钥>')
)

• 若使用Synapse专用SQL池，虽理论上支持多值INSERT，但仍存在兼容性问题，上述方案同样适用；
• 大数据量场景下，COPY INTO能最大程度降低网络开销，远优于逐条插入的效率。

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