How to Generate SQL INSERT Test Data Quickly

Writing SQL INSERT statements by hand is tedious, error-prone, and doesn't scale. Whether you need 10 rows for a quick test or 100,000 rows for a performance benchmark, there are faster ways. This guide covers the quickest approaches to generating realistic SQL test data.

Method 1: Dummy JSON Generator (No Code)

Dummy JSON Generator has a built-in SQL export mode. You configure your fields, set a record count, switch to SQL output, and download a .sql file with batched INSERT statements ready to execute.

A generated SQL file for a users table looks like this:

INSERT INTO records (id, fullName, email, city, status, createdAt) VALUES
('a3f8b2c1-4e5d-6f7a-8b9c', 'Ayesha Rahman', 'ayesha@example.com', 'Lahore', 'active', '2024-08-14 09:33:21'),
('b7c3d4e5-5f6a-7b8c-9d0e', 'James O''Brien', 'james@example.com', 'Dublin', 'inactive', '2025-01-02 16:47:05'),
('c8d4e5f6-6a7b-8c9d-0e1f', 'Priya Sharma', 'p.sharma@example.com', 'Mumbai', 'active', '2023-11-22 14:20:00');

Note that the tool handles SQL string escaping automatically — the apostrophe in O'Brien becomes O''Brien (standard SQL escaping).

Customising the Table Name

By default the SQL output uses records as the table name. Before running the file, do a find-and-replace to use your actual table name:

# sed replacement (Linux/macOS)
sed -i 's/INSERT INTO records/INSERT INTO users/g' generated-data.sql

# PowerShell (Windows)
(Get-Content generated-data.sql) -replace 'INSERT INTO records', 'INSERT INTO users' | Set-Content generated-data.sql

Running the SQL File

PostgreSQL

psql -U postgres -d mydb -f generated-data.sql

MySQL / MariaDB

mysql -u root -p mydb < generated-data.sql

SQLite

sqlite3 mydb.db < generated-data.sql

SQL Server

sqlcmd -S localhost -d mydb -i generated-data.sql

Method 2: Write SQL Generators in Pure SQL

PostgreSQL and MySQL both support generating data using built-in functions — useful when you want something self-contained without external tools.

PostgreSQL: generate_series()

INSERT INTO users (id, email, status, created_at)
SELECT
    gen_random_uuid(),
    'user' || i || '@example.com',
    (ARRAY['active', 'inactive', 'pending'])[floor(random() * 3 + 1)],
    NOW() - (random() * interval '2 years')
FROM generate_series(1, 10000) AS s(i);

This generates 10,000 rows entirely within PostgreSQL — no external file needed. The data is less realistic (emails follow a pattern like user1@example.com) but it's fast and self-contained.

MySQL: Stored Procedure Loop

DELIMITER //
CREATE PROCEDURE seed_users(IN count INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    WHILE i < count DO
        INSERT INTO users (email, status, created_at)
        VALUES (
            CONCAT('user', i, '@example.com'),
            ELT(FLOOR(RAND() * 3) + 1, 'active', 'inactive', 'pending'),
            DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 730) DAY)
        );
        SET i = i + 1;
    END WHILE;
END //
DELIMITER ;

CALL seed_users(10000);
DROP PROCEDURE seed_users;

Method 3: Node.js Script for Complex Schemas

For schemas with multiple related tables or complex constraints, generate SQL programmatically:

import { faker } from '@faker-js/faker';
import { createWriteStream } from 'fs';

const out = createWriteStream('seed.sql');
const USERS = 1000;
const ORDERS_PER_USER = 5;

// Generate users
out.write('-- Users\n');
const userIds = [];
for (let i = 0; i < USERS; i++) {
    const id = faker.string.uuid();
    userIds.push(id);
    const name = faker.person.fullName().replace("'", "''");
    const email = faker.internet.email();
    const date = faker.date.past({ years: 2 }).toISOString().slice(0, 19).replace('T', ' ');
    out.write(`INSERT INTO users VALUES ('${id}', '${name}', '${email}', '${date}');\n`);
}

// Generate orders referencing real user IDs
out.write('\n-- Orders\n');
for (const userId of userIds) {
    for (let j = 0; j < ORDERS_PER_USER; j++) {
        const orderId = faker.string.uuid();
        const amount = faker.commerce.price({ min: 10, max: 500 });
        const status = faker.helpers.arrayElement(['pending', 'shipped', 'delivered', 'cancelled']);
        out.write(`INSERT INTO orders VALUES ('${orderId}', '${userId}', ${amount}, '${status}');\n`);
    }
}
out.end();

This pattern ensures referential integrity — every order's user_id points to a real user that was just inserted.

Performance Tips for Large INSERT Files

Use batched INSERTs. INSERT INTO t VALUES (1,...),(2,...),(3,...) is dramatically faster than individual INSERT statements. Dummy JSON Generator batches 1,000 rows per statement automatically.

Wrap in a transaction.

BEGIN;
-- your INSERT statements
COMMIT;

Disable indexes during bulk insert (PostgreSQL):

-- Drop indexes, insert data, recreate indexes
ALTER TABLE users DISABLE TRIGGER ALL;
-- ... your inserts ...
ALTER TABLE users ENABLE TRIGGER ALL;
REINDEX TABLE users;

Use COPY instead of INSERT for very large datasets — it's 5–20x faster. Export as CSV from Dummy JSON Generator and use \COPY in psql.

Need the SQL file itself? Open the tool, configure your fields, switch to SQL mode, and download. Ready to run in under a minute.