I am trying to speed up the pandas .to_sql() function as it currently takes ~30 mins to dump a table of 22 columns and 100K rows to a MS SQL Server Db. I've tried using the method='multi' and chunksize=1000 (I've read is the max for sql server) but getting the following error, a bunch of ?s in the error, and my data in the [parameters: section of the error:
DBAPIError: (pyodbc.Error) ('07002', '[07002] [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error (0) (SQLExecDirectW)')
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
[parameters: (one big tuple)]
Here is the code I am using:
user_name = 'username'
cred = open('filename', 'r').read()
server_name = 'XXXXXXXX'
port = 'XXXX'
DB = 'database'
driver = 'ODBC Driver 17 for SQL Server'
conn = create_engine('mssql+pyodbc://'+user_name+':'+cred+'@'+server_name+':'+port+'/'+DB+'?driver='+driver)
df.to_sql('test_table', con=conn, if_exists='replace', schema='dbo', method='multi', chunksize= 1000)
Any ideas on what is happening here or another alternative to speed this up?
