How to speed up bulk insert to MS SQL Server using pyodbc

As noted in a comment to another answer, the T-SQL BULK INSERT command will only work if the file to be imported is on the same machine as the SQL Server instance or is in an SMB/CIFS network location that the SQL Server instance can read. Thus it may not be applicable in the case where the source file is on a remote client.

pyodbc 4.0.19 added a Cursor#fast_executemany feature which may be helpful in that case. fast_executemany is "off" by default, and the following test code ...

cnxn = pyodbc.connect(conn_str, autocommit=True)crsr = cnxn.cursor()crsr.execute("TRUNCATE TABLE fast_executemany_test")sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"params = [(f'txt{i:06d}',) for i in range(1000)]t0 = time.time()crsr.executemany(sql, params)print(f'{time.time() - t0:.1f} seconds')

... took approximately 22 seconds to execute on my test machine. Simply adding crsr.fast_executemany = True ...

cnxn = pyodbc.connect(conn_str, autocommit=True)crsr = cnxn.cursor()crsr.execute("TRUNCATE TABLE fast_executemany_test")crsr.fast_executemany = True  # new in pyodbc 4.0.19sql = "INSERT INTO fast_executemany_test (txtcol) VALUES (?)"params = [(f'txt{i:06d}',) for i in range(1000)]t0 = time.time()crsr.executemany(sql, params)print(f'{time.time() - t0:.1f} seconds')

... reduced the execution time to just over 1 second.

Update - July 2021: bcpyaz is a wrapper for Microsoft's bcp utility.

Update - April 2019: As noted in the comment from @SimonLang, BULK INSERT under SQL Server 2017 and later apparently does support text qualifiers in CSV files (ref: here).

BULK INSERT will almost certainly be much faster than reading the source file row-by-row and doing a regular INSERT for each row. However, both BULK INSERT and BCP have a significant limitation regarding CSV files in that they cannot handle text qualifiers (ref: here). That is, if your CSV file does not have qualified text strings in it ...

1,Gord Thompson,2015-04-152,Bob Loblaw,2015-04-07

... then you can BULK INSERT it, but if it contains text qualifiers (because some text values contains commas) ...

1,"Thompson, Gord",2015-04-152,"Loblaw, Bob",2015-04-07

... then BULK INSERT cannot handle it. Still, it might be faster overall to pre-process such a CSV file into a pipe-delimited file ...

1|Thompson, Gord|2015-04-152|Loblaw, Bob|2015-04-07

... or a tab-delimited file (where → represents the tab character) ...

1→Thompson, Gord→2015-04-152→Loblaw, Bob→2015-04-07

... and then BULK INSERT that file. For the latter (tab-delimited) file the BULK INSERT code would look something like this:

import pypyodbcconn_str = "DSN=myDb_SQLEXPRESS;"cnxn = pypyodbc.connect(conn_str)crsr = cnxn.cursor()sql = """BULK INSERT myDb.dbo.SpikeData123FROM 'C:\\__tmp\\biTest.txt' WITH (    FIELDTERMINATOR='\\t',    ROWTERMINATOR='\\n'    );"""crsr.execute(sql)cnxn.commit()crsr.close()cnxn.close()

Note: As mentioned in a comment, executing a BULK INSERT statement is only applicable if the SQL Server instance can directly read the source file. For cases where the source file is on a remote client, see this answer.

yes bulk insert is right path for loading large files into a DB. At a glance I would say that the reason it takes so long is as you mentioned you are looping over each row of data from the file which effectively means are removing the benefits of using a bulk insert and making it like a normal insert. Just remember that as it's name implies that it is used to insert chucks of data.I would remove loop and try again.

Also I'd double check your syntax for bulk insert as it doesn't look correct to me. check the sql that is generated by pyodbc as I have a feeling that it might only be executing a normal insert

Alternatively if it is still slow I would try using bulk insert directly from sql and either load the whole file into a temp table with bulk insert then insert the relevant column into the right tables. or use a mix of bulk insert and bcp to get the specific columns inserted or OPENROWSET.