Why Bulk Insert Beats Row-by-Row
Inserting one row per statement works for demos and dies at 100,000 rows. Bulk paths amortize parsing, planning, and disk I/O. For production CSV loads, prefer server-side bulk commands or multi-row INSERT batches—not a loop of single inserts from your app.
Choose the Right Bulk Path
Match the tool to your database and environment.
| Method | Engine | Best for |
|---|---|---|
| LOAD DATA [LOCAL] INFILE | MySQL / MariaDB | Fast server-side CSV load |
| COPY ... FROM | PostgreSQL | Fastest Postgres CSV import |
| Multi-row INSERT | Most engines | Portable batches, smaller files |
| Generated INSERT script | Any SQL DB | One-off loads, air-gapped setups |
Prepare the CSV First
Most bulk failures are dirty files, not slow SQL.
- UTF-8 encoding, header row matching column names or order.
- Consistent delimiter (comma vs semicolon).
- Dates in ISO 8601 (YYYY-MM-DD).
- IDs kept as text when leading zeros matter.
- No summary rows, formulas, or merged cells from Excel.
Clean in the browser
Use the Online CSV Editor to fix headers and blank rows, or convert Excel with Excel to CSV before loading. For INSERT scripts, the CSV to SQL Converter generates statements you can review.
Step-by-Step: PostgreSQL COPY
COPY is the default high-speed path on Postgres.
COPY orders (order_id, email, amount)
FROM '/path/orders.csv'
WITH (FORMAT csv, HEADER true, ENCODING 'UTF8');From the client (\copy)
When the file lives on your laptop, not the server filesystem, use psql \copy—which streams through the client connection.
\copy orders (order_id, email, amount) FROM 'orders.csv' WITH (FORMAT csv, HEADER true)Step-by-Step: MySQL LOAD DATA
LOAD DATA reads CSV directly into a table.
LOAD DATA LOCAL INFILE '/path/orders.csv'
INTO TABLE orders
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(order_id, email, amount);Step-by-Step: Multi-Row INSERT Batches
When you cannot use COPY/LOAD DATA (managed permissions, SQLite, portable scripts), batch inserts still beat single rows.
INSERT INTO orders (order_id, email, amount) VALUES
('1001', 'ada@example.com', 42.50),
('1002', 'grace@example.com', 19.00),
('1003', 'alan@example.com', 7.25);Sizing batches
Start around 500–2,000 rows per statement. Too large hits max_allowed_packet or query size limits; too small wastes round trips.
Transactions, Indexes, and Speed
Bulk loads go faster when you plan the table state.
- Wrap batches in a transaction when safe.
- For huge one-time loads, consider dropping non-essential indexes and rebuilding after.
- Disable triggers only when you understand the side effects.
- Load into a staging table, then INSERT…SELECT into production with validation.
Real-World Examples
Patterns that show up in production.
Nightly warehouse sync
Ops drops a UTF-8 CSV on SFTP; a job runs COPY into a staging table and merges into fact tables.
App admin import
A SaaS feature accepts CSV, validates in the app, then writes multi-row INSERTs in chunks of 1,000.
Air-gapped migration
Engineers generate SQL INSERT files with CSV to SQL, review them offline, and execute on the target server.
Common Mistakes
These turn a five-minute load into an all-nighter.
- Row-by-row inserts for millions of rows.
- Wrong delimiter (semicolon CSV loaded as comma).
- Header row inserted as data because IGNORE/HEADER was omitted.
- Encoding mismatches corrupting names.
- Loading straight into production without a staging table.
Best Practices
A short checklist before every bulk load.
- Validate CSV shape and encoding first.
- Prefer COPY / LOAD DATA when available.
- Use staging tables for non-trivial imports.
- Log row counts before and after.
- Keep generated SQL or load scripts in version control for reruns.
Why Use Convert CSV Online?
Convert CSV Online is free, browser-based, and requires no account for everyday conversions. Clean files in the Online CSV Editor, generate INSERT scripts with CSV to SQL, or prep Excel exports with Excel to CSV before your database load. Client-side workflows work on Windows, macOS, and Linux browsers.
Preview before you COPY
Catch delimiter and header issues in the browser so the database never sees a bad file.
Conclusion
SQL bulk insert is mostly file prep plus the right engine feature—COPY, LOAD DATA, or batched INSERT. Clean the CSV, load into staging, verify counts, then promote.
FAQ
What is the fastest way to bulk insert CSV into PostgreSQL?
Use COPY (or psql \copy from a client file) with FORMAT csv and HEADER true. It outperforms row-by-row INSERT by a wide margin.
How do I bulk insert CSV into MySQL?
Use LOAD DATA INFILE or LOAD DATA LOCAL INFILE with matching field/line terminators, and IGNORE 1 LINES for a header row.
When should I use multi-row INSERT instead?
When server file access is blocked, you need portable SQL, or you are loading into engines without COPY/LOAD DATA.
How big should INSERT batches be?
Often 500–2,000 rows. Tune for your max packet/query size and memory limits.
Should I load directly into production tables?
Prefer a staging table, validate row counts and samples, then merge into production.
How do I generate INSERT statements from CSV?
Use Convert CSV Online’s CSV to SQL Converter for reviewable scripts, especially for smaller or offline loads.
References
Convert your CSV in the browser
Preview, clean, and convert CSV files free with Convert CSV Online—no installation and no account required for everyday conversions.