To generate INSERT SQL from data that already exists, the fastest path depends on the engine: pg_dump --inserts for Postgres, mysqldump --skip-extended-insert for MySQL, or the SSMS Generate Scripts wizard set to data-only for SQL Server — all three turn existing rows into INSERT statements you can read, version-control, and replay elsewhere.
Generating INSERT statements is a portability and readability tool, not a bulk-performance one. It is the right call for seeding a new environment, moving a handful of rows between different database engines, or committing fixture data to a repository — and the wrong call for moving a hundred million rows, where COPY or LOAD DATA will finish before pg_dump has written its first batch of INSERTs.
Table of contents
- Postgres: pg_dump —inserts and —column-inserts
- MySQL: mysqldump —skip-extended-insert
- SQL Server: SSMS Generate Scripts, data only
- Writing your own SELECT that builds INSERT strings
- When to use COPY or LOAD DATA instead
- Practical use: seeding a new environment
- Making a generated seed script idempotent
- How this fits the rest of the stack
- FAQ
Postgres: pg_dump —inserts and —column-inserts
pg_dump can emit data as INSERT statements instead of its default COPY format, which is what you want when the output needs to be readable, diffable, or portable to a database that is not Postgres.
# One INSERT per row, no column names
pg_dump --data-only --inserts --table=users mydb > users_insert.sql
# Same, but with explicit column names on every INSERT
pg_dump --data-only --column-inserts --table=users mydb > users_insert_named.sql--inserts alone omits column names, which makes the file smaller but fragile: if the target table’s column order ever differs from the source, the insert silently puts values in the wrong columns instead of failing. --column-inserts is slower to generate and produces a larger file, and it is the one I actually use, because a loud failure on a missing column beats a silent one on a misaligned value every time.
MySQL: mysqldump —skip-extended-insert
mysqldump defaults to extended-insert syntax — one INSERT statement carrying dozens or hundreds of rows in a single VALUES list — because it is faster to load. That same format is nearly impossible to read or diff in a code review, which is the tradeoff --skip-extended-insert exists to reverse.
mysqldump --no-create-info --skip-extended-insert --complete-insert mydb users > users_insert.sql--complete-insert adds explicit column names to every statement, the same safety --column-inserts gives you on the Postgres side. --no-create-info skips the CREATE TABLE statement, which you want when you are only after the data because the schema already exists on the target.
SQL Server: SSMS Generate Scripts, data only
SQL Server has no command-line equivalent as simple as the two above; the standard path runs through SSMS’s GUI.
- Right-click the database in Object Explorer, then Tasks, then Generate Scripts
- Choose “Select specific database objects” and pick the tables you need
- On the “Set Scripting Options” screen, click Advanced
- Find “Types of data to script” and set it to “Data only” (or “Schema and data” if you also want the CREATE TABLE)
- Finish the wizard and SSMS writes one file of INSERT statements per selected table
This generates the same shape of output as --column-inserts: one INSERT per row with explicit column names. For a handful of tables this is genuinely the fastest path; for dozens of tables the wizard’s per-table file output gets tedious fast, and a scripted approach is worth the setup time instead.
Writing your own SELECT that builds INSERT strings
Sometimes you need to generate inserts for a handful of rows without installing any tooling — a debugging session, a one-off data fix, a row you need to hand to a teammate on Slack. Postgres has a clean built-in for this with format():
SELECT format(
'INSERT INTO users (id, email, created_at) VALUES (%s, %L, %L);',
id, email, created_at
)
FROM users
WHERE id = 42;%L quotes and escapes the value as a SQL literal automatically, which is the part people get wrong doing this by hand with string concatenation. SQL Server has no equivalent built-in, so the same job there means manual concatenation and manual escaping of embedded quotes:
SELECT 'INSERT INTO dbo.Users (Id, Email, CreatedAt) VALUES ('
+ CAST(Id AS VARCHAR(20)) + ', '''
+ REPLACE(Email, '''', '''''') + ''', '''
+ CONVERT(VARCHAR(33), CreatedAt, 126) + ''');'
FROM dbo.Users
WHERE Id = 42;The REPLACE(Email, '''', '''''') is doubling every single quote in the value so it does not terminate the string early — the one detail that breaks this approach the first time someone’s name or email contains an apostrophe.
When to use COPY or LOAD DATA instead
INSERT statements are for humans and for portability. COPY (Postgres) and LOAD DATA INFILE (MySQL) are for volume, and the difference is not small — both parse a bulk text or binary stream directly into the table’s storage format instead of executing SQL statement by statement, which is an order of magnitude faster on real datasets.
If the destination is the same engine as the source and the dataset is more than a few thousand rows, reach for COPY or LOAD DATA first. Reach for generated INSERT statements when the destination is a different engine, when the file needs to be readable in a code review, or when the row count is small enough that raw throughput does not matter.
Practical use: seeding a new environment
The most common real reason to generate INSERT SQL is seeding — a staging environment, a local dev database, a fresh instance after a migration. A small, hand-curated set of INSERT statements committed to the repository next to your migrations gives every new environment the same starting data without anyone remembering to run a separate import step.
Keep the seed file small and deliberate rather than dumping a full production table into it. A seed script with ten representative rows per table is more useful long-term than one with fifty thousand, because it stays fast to run and easy to reason about when someone asks “why does staging have this weird row.”
Making a generated seed script idempotent
A raw INSERT script fails the second time it runs, the moment it hits a duplicate primary key or a unique constraint on an environment that already has data. If the seed script is meant to be safe to re-run — after a schema reset, in CI on every build — build that in rather than relying on always running against an empty database.
-- Postgres: skip rows that already exist
INSERT INTO users (id, email, created_at) VALUES (1, '[email protected]', now())
ON CONFLICT (id) DO NOTHING;
-- MySQL: same intent
INSERT IGNORE INTO users (id, email, created_at) VALUES (1, '[email protected]', NOW());ON CONFLICT DO NOTHING and INSERT IGNORE both turn a would-be error into a silent no-op, which is exactly the behavior a repeatable seed script wants. SSMS-generated scripts for SQL Server do not add this automatically, so a manual IF NOT EXISTS guard or a MERGE statement is the equivalent step if you are scripting SQL Server data by hand.
How this fits the rest of the stack
Once the data is portable, the database it is moving to matters as much as the export format. Whatever runs the seed script needs a real connection string to the target database, and the RunxBuild dashboard hands you that string the moment the database provisions rather than making you assemble it by hand. A managed Postgres or MySQL instance with backups and connection limits already handled means the seed script is the only migration step left, not the first of several. The RunxBuild hosting calculator shows what that managed database costs alongside the service that will actually run the seed script.
Useful related references:
- INSERT INTO in MySQL: Single Rows, Bulk Inserts, and the Upsert You Actually Want
- MySQL to MySQL: Migrating a Database Between Servers
- PostgreSQL Backup: pg_dump, pg_basebackup, and WAL Archiving
- Database backups on RunxBuild
FAQ
How do I generate INSERT statements from an existing SQL Server table?
Use the SSMS Generate Scripts wizard: right-click the database, choose Tasks then Generate Scripts, select your tables, and under Advanced set “Types of data to script” to “Data only”. SSMS writes one INSERT statement per row with explicit column names.
Does pg_dump —inserts include the table schema?
Not by itself. Pair it with —data-only to get rows only, assuming the schema already exists on the target, or drop —data-only (and add —schema-only separately, or omit both flags) if you want a combined schema-and-data dump.
What is the difference between COPY and INSERT in PostgreSQL?
COPY streams rows directly into the table’s storage in bulk and is dramatically faster for large volumes. INSERT executes one statement (or one multi-row VALUES list) at a time through the normal SQL parser and planner, which is slower per row but produces human-readable, portable SQL text.
Why does mysqldump produce hard-to-read INSERT statements by default?
By default mysqldump uses extended-insert syntax, packing many rows into a single INSERT with a long VALUES list, because it loads faster on import. Pass —skip-extended-insert to get one INSERT per row instead, which is far easier to read, diff, and review.
Can I generate INSERT statements for just a few rows instead of a whole table?
Yes. Most tools support filtering: pg_dump accepts a —table flag but dumps that whole table, so for a row-level subset write a SELECT with format() (Postgres) or manual string concatenation (SQL Server) and a WHERE clause, or filter first in SSMS’s Generate Scripts advanced options.