Generate SQL Test Data: INSERT Statements in Seconds

A few hundred realistic rows for a new table? A few thousand for a performance test? A demo dump with proper names and addresses? Generating SQL test data is one of the most common recurring developer tasks – and one where you can easily waste hours when doing it by hand.

What makes test data "realistic"

Dummy data (John Doe, 123 Main St, New York) does not stress-test anything. Useful test data needs:

• Variability in names, ZIP codes, streets and cities
• Format compliance: valid IBAN check digits, correct ZIP length, plausible birthdays
• Localization: German umlauts, country codes, phone number formats
• No real people – GDPR friendly

From generator to INSERT statement

Use the test data generator to create a dataset, e.g. 500 German customers with name, address, email, phone and IBAN. For the database import, convert the export to an INSERTstatement – either directly in the generator or with a quick transformation.

Example: MySQL / MariaDB

INSERT INTO customers (first_name, last_name, street, zip, city, email, iban) VALUES
('Lukas', 'Weber', 'Lindenstraße 42', '10115', 'Berlin', 'lukas.weber@example.de', 'DE89370400440532013000'),
('Marie', 'Schmidt', 'Hauptplatz 7', '80331', 'München', 'marie.schmidt@example.de', 'DE12500105170648489890'),
('Jonas', 'Fischer', 'Am Markt 3', '20095', 'Hamburg', 'jonas.fischer@example.de', 'DE44100500000012345678');

PostgreSQL (with RETURNING)

INSERT INTO customers (first_name, last_name, street, zip, city, email, iban)
VALUES ($1, $2, $3, $4, $5, $6, $7)
RETURNING id;

For bulk loads Postgres' COPY FROM from a CSV file is faster than many single INSERTs.

SQLite (via .read)

-- seed.sql
BEGIN TRANSACTION;
INSERT INTO customers (first_name, last_name, email) VALUES ('Lukas', 'Weber', 'lukas.weber@example.de');
INSERT INTO customers (first_name, last_name, email) VALUES ('Marie', 'Schmidt', 'marie.schmidt@example.de');
COMMIT;

-- Import
.read seed.sql

Typical columns and types

Formatting and consistency

Long INSERT blocks become unreadable fast. Use the SQL formatter to format or minify statements before committing. For migrations, keep seed data in a separate seed.sql so it does not collide with schema migrations.

Test data vs. production data

• Never copy production data into test environments – GDPR disaster.
• Never embed real IBANs, even your own – use generated fake IBANs instead.
• Deterministic seeds help with debugging: a fixed seed produces reproducible data.

In practice: fill a table with 1000 rows

Workflow in under 2 minutes:

1. Pick the fields you need in the test data generator (name, address, IBAN…).
2. Set the count to 1000.
3. Export as SQL INSERT.
4. Tidy up with the SQL formatter.
5. Paste into your DB console and run.

The result: realistic yet harmless data for your dev, staging or demo environment – without any GDPR risk.