Dev Basics · 4 min read
SQL INSERT Statements: Single Rows, Bulk Inserts, and Upserts
Loading data into a database — seed data, a spreadsheet import, a migration — usually comes down to INSERT statements. A few habits make them faster and safer.
Try it yourself with the related tool.
Generate INSERT statements →Advertisement
The basic statement
INSERT INTO users (name, email) VALUES ('Ana', '[email protected]'); — always list the columns explicitly. Relying on column order breaks as soon as someone adds or reorders a column, and explicit columns let defaults and auto-increment IDs fill themselves in.
Batch rows together
A multi-row insert — VALUES (...), (...), (...) — sends many rows in one statement and is dramatically faster than one statement per row, because each statement has its own round trip and commit overhead. Batches of a few hundred to a few thousand rows work well. Wrapping a large load in a single transaction helps further.
For very large files, use the database's bulk loader, such as PostgreSQL's COPY or MySQL's LOAD DATA.
Escaping values
In a SQL string literal, a single quote is escaped by doubling it: 'O''Brien'. Generated scripts must escape every value, and NULL must be written without quotes. In application code, never build SQL by concatenating user input — use parameterised queries to prevent SQL injection.
Upserts
To insert a row or update it if it already exists, PostgreSQL and SQLite use INSERT ... ON CONFLICT (email) DO UPDATE SET name = excluded.name, while MySQL uses ON DUPLICATE KEY UPDATE. Upserts need a unique constraint to detect the conflict.