Python SQL Server Batch Insert: Step-by-Step Guide

Python can connect to SQL Server with only a few lines of code, but inserting large datasets one row at a time creates unnecessary network round-trips. This guide builds a small example and compares row-by-row inserts, executemany(), fast_executemany, SQLAlchemy, and BCP so you can choose a technique that fits your data volume and transaction requirements.

1. Install the prerequisites

Install Microsoft's ODBC Driver 18 for SQL Server for your operating system, then install the Python packages used in the examples:

python -m pip install pyodbc sqlalchemy pandas

Check that the driver is available to pyodbc:

import pyodbc

print(pyodbc.drivers())

2. Create a destination table

For a repeatable test, create a table with columns matching the rows that Python will insert. This example lets SQL Server generate the primary key:

CREATE TABLE dbo.CustomerImport
(
    CustomerID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerName VARCHAR(100),
    EmailAddress VARCHAR(200),
    City VARCHAR(100)
);

3. Connect with pyodbc

For Windows authentication, use a connection string like this and replace the server and database names with your own. With ODBC Driver 18, encryption is enabled; use a valid server certificate in production rather than bypassing certificate validation.

import pyodbc

connection_string = (
    "DRIVER={ODBC Driver 18 for SQL Server};"
    "SERVER=SQLSERVER01;"
    "DATABASE=SalesDB;"
    "Trusted_Connection=yes;"
    "Encrypt=yes;"
)

connection = pyodbc.connect(connection_string)
cursor = connection.cursor()

If you use SQL authentication, keep credentials outside source code—for example, in a protected secret store or environment variables—and use a least-privilege database account. Verify the connection before loading data:

cursor.execute("SELECT @@VERSION")
print(cursor.fetchone()[0])

4. Prepare rows and use a parameterized batch insert

Use parameter placeholders rather than building SQL by concatenating values. The tuple order must match the column order in the insert statement.

data = [
    (f"Customer {i}", f"customer{i}@example.com", "New York")
    for i in range(100_000)
]

insert_sql = """
    INSERT INTO dbo.CustomerImport
        (CustomerName, EmailAddress, City)
    VALUES (?, ?, ?)
"""

cursor.executemany(insert_sql, data)
connection.commit()

executemany() is a straightforward improvement over issuing an execute() call for every row. It reduces application-level overhead, though performance depends on the driver, server, network, schema, and transaction size.

5. Enable fast_executemany and load in batches

For many SQL Server ODBC workloads, pyodbc's fast_executemany can improve bulk insert throughput. Benchmark it with your driver and actual column types, and split large inputs into bounded batches so memory and transaction sizes remain manageable.

batch_size = 10_000
cursor.fast_executemany = True

try:
    for start in range(0, len(data), batch_size):
        batch = data[start:start + batch_size]
        cursor.executemany(insert_sql, batch)
        connection.commit()
except Exception:
    connection.rollback()
    raise
finally:
    cursor.close()
    connection.close()

With a commit after each batch, earlier batches remain committed if a later batch fails. If the entire load must be atomic, use one transaction and commit only after all batches succeed, while accounting for the larger transaction log usage and longer-held locks. Always preserve errors and roll back the active transaction on failure.

6. Use SQLAlchemy or pandas when they fit your workflow

SQLAlchemy provides connection pooling and a higher-level database interface. Its pyodbc dialect can enable the same optimization:

from sqlalchemy import create_engine

engine = create_engine(
    connection_string,
    fast_executemany=True,
)

For a pandas DataFrame, append rows with to_sql(). Choose a suitable chunk size, and test the generated SQL and throughput with your schema and SQLAlchemy version.

df.to_sql(
    "CustomerImport",
    engine,
    schema="dbo",
    if_exists="append",
    index=False,
    chunksize=10_000,
)

7. Consider BCP for very large loads

For millions of rows, SQL Server's Bulk Copy Program (BCP) may be a better fit than row-oriented inserts. Export the data to a file, then invoke BCP using a process argument list and integrated authentication, for example:

import subprocess

subprocess.run(
    [
        "bcp",
        "SalesDB.dbo.CustomerImport",
        "in",
        "customers.csv",
        "-S", "SQLSERVER01",
        "-T",
        "-c",
        "-t,",
    ],
    check=True,
)

Make sure the file format, field terminators, column order, and null handling match the target table. A simple comma delimiter is not a full CSV parser for values containing commas or quotes; use a suitable BCP format file or a properly prepared input format. Install the BCP utility and grant the loading account only the permissions it needs.

8. Tune and verify the load

• Start with a moderate batch size such as 1,000 or 10,000 rows, then benchmark alternatives against representative data.
• Measure end-to-end time, memory, transaction-log growth, and server load; performance ratios vary widely between environments.
• For large imports, consider a staging table to validate data before applying it to production tables.
• Indexes and constraints affect insert cost. Change them only with a tested plan and restore or validate them after the load.
• Handle duplicates and retries deliberately so that a restarted load does not silently create incorrect data.