Writing bulk INSERT scripts that load quickly

Why one statement per row is slow

The obvious output of any INSERT generator is one statement per row:

INSERT INTO Customer (Id, Name) VALUES (1, 'Ada');
INSERT INTO Customer (Id, Name) VALUES (2, 'Grace');
INSERT INTO Customer (Id, Name) VALUES (3, 'Alan');

It is correct, readable and easy to generate. It is also the slowest reasonable option, for reasons that have little to do with writing the rows.

Each statement is parsed and planned. Each is a separate round trip unless the client batches them. And unless they are wrapped in a transaction, each one commits on its own — which on most engines means a synchronous flush of the transaction log to durable storage before the next statement starts. That flush, not the insert, is what dominates the time.

Multi-row VALUES, and the limits

Standard SQL lets one INSERT carry many rows:

INSERT INTO Customer (Id, Name) VALUES
  (1, 'Ada'),
  (2, 'Grace'),
  (3, 'Alan');

One parse, one plan, one statement, one round trip. This is the single biggest improvement available to a generated script, and it is supported by SQL Server, PostgreSQL, MySQL and SQLite.

Three hard limits decide how many rows to put in each statement.

Row count per statement

SQL Server accepts at most 1,000 rows in a single VALUES clause. Exceed it and the statement fails outright. PostgreSQL and MySQL have no fixed row limit, but they do have limits on statement size.

Parameter count

If the statement is parameterised rather than built from literals, the parameter limit usually binds first. SQL Server allows 2,100 parameters per statement, so a ten-column insert reaches the ceiling at 210 rows, well before the 1,000-row limit. PostgreSQL's protocol limit is 65,535 parameters. Both produce confusing errors that name the parameter count rather than the row count, so it is worth knowing which one you have hit.

Statement size

MySQL rejects any packet larger than max_allowed_packet, commonly 64 MB but often much smaller on shared hosting. A batch of wide rows with long text columns hits this before any row count matters.

Practical batch sizes, given all three: 500 to 1,000 rows for literal scripts on most engines, 200 or fewer for parameterised inserts on SQL Server, and smaller again where rows are wide. The returns flatten quickly — most of the gain is in the first hundred rows per statement — so there is no reason to push against the limits.

Transaction size is its own decision

Batching rows into statements and batching statements into transactions are separate choices, and the second is often the one that matters more.

Wrapping the whole load in one transaction gives all-or-nothing behaviour and one commit. It also means the transaction log must hold every change until the commit, locks are held for the duration, and a failure at 99% rolls back everything — which can take as long as the insert did.

Committing every few thousand rows keeps the log bounded and makes a failure recoverable from a known point. The cost is that a partial load is now a real state you have to be able to detect and resume from.

A reasonable default for a generated script is a commit every 5,000 to 10,000 rows, with a comment marking each checkpoint so a human can see how far a failed run got. For loads that must be atomic, use one transaction and make sure the log has room for the whole thing.

Identity columns and explicit keys

If the target table generates its own keys, a script that supplies them needs to say so explicitly, and each engine says it differently.

SQL Server requires SET IDENTITY_INSERT dbo.Customer ON before the inserts and OFF after. Only one table per session may have it on, so a multi-table script must turn it off before moving to the next table — a common cause of a script that works in isolation and fails when concatenated with another.

PostgreSQL will accept an explicit value for a serial or identity column, but the underlying sequence is not advanced. The load succeeds and the next ordinary insert fails with a duplicate key, because the sequence is still pointing at 1. A generated script should reset it with setval afterwards. This one is worth flagging loudly, because the failure appears long after the import, in unrelated code.

MySQL advances AUTO_INCREMENT past any explicit value, so it generally behaves.

Literals, dates and the things that silently change

A literal script has no parameters, which means every value has to survive being written as text and read back. Four places where that goes wrong:

• Quotes in strings. A single quote inside a value must be doubled. Anything else is both a syntax error and, if the input is untrusted, an injection. Never build a load script by concatenating unescaped input.
• Dates. Write an unambiguous format and nothing else. '2026-03-04' is 4 March or 3 April depending on the server's language setting. For SQL Server, '2026-03-04T09:30:00' is interpreted consistently regardless of locale.
• Decimals. Never emit a floating point literal for money. 0.1 written as a float is not 0.1, and a sum over a million rows shows it. Use exact decimal types and exact literals.
• NULL versus empty. NULL and '' are different values, and a source file cannot usually tell you which was meant. Decide once, apply it consistently, and write it down.

Defaults worth starting from

• Multi-row VALUES, around 500 rows per statement for literal scripts.
• For parameterised inserts, divide the engine's parameter limit by your column count and stay well under it.
• An explicit transaction with a commit every 5,000 to 10,000 rows, unless the load must be atomic.
• Identity handling stated explicitly, and sequences reset afterwards on PostgreSQL.
• ISO date literals, exact decimals, doubled quotes, and one documented rule for NULL.

Above roughly a million rows, stop generating INSERT statements. Every engine has a bulk path — BULK INSERT and bcp, COPY, LOAD DATA INFILE — that takes a different and much faster route into the storage engine. Generated INSERT scripts are for seeding, fixtures and migrations, which is the range the SQL INSERT Generator and CSV to SQL are built for.