How to Generate Test Data in SQL — Sequences, Random Values, and a Million Rows

1. Why You Need Generated Test Data

There are three distinct reasons to generate data in SQL, and they have different requirements. Confusing them is why test suites pass in CI and fall over on the first real deployment.

• Development. You need enough rows that joins return more than one page, pagination hits the second page, and NOT NULL and unique constraints actually fire. Ten rows hide every off-by-one in a window function. You also want the data to be cheap to throw away and regenerate, because the schema will change tomorrow.
• Load and stress testing. Here volume is the point. You need a million orders to see whether the query does a full scan at that size, how the buffer pool behaves, and whether the index you added is still chosen when 30% of the table shares one value. Random data with the wrong cardinality — every row a different customer — makes an index look artificially good.
• Demos and screenshots. You need data that looks plausible to a human: real-looking names, coherent dates, totals that aren't all exactly 100.00, and a foreign-key graph that doesn't produce empty grids in the UI.

The shared constraint is that the data has to be generated, not found. Production data carries customer information you cannot ship to a laptop or a contractor, and hand-written fixtures don't scale past a screenful.

2. Where Hand-Written INSERT Statements Fall Apart

The starting point everyone writes looks like this, and for seeding reference data it is perfectly correct:

INSERT INTO customers (name, email, created_at) VALUES
  ('Alice',  'alice@example.com',  '2024-01-01'),
  ('Bob',    'bob@example.com',    '2024-01-02'),
  ('Carol',  'carol@example.com',  '2024-01-03');
-- ...and 47 more lines you typed by hand

Four problems show up as soon as you scale this:

• It doesn't scale past a page. Fifty rows is a minute of work. Fifty thousand is not something a person types, and a generated file of literals still has to be pasted, escaped, and re-pasted every time the schema changes.
• The distribution is fake. Every hand-written fixture has uniform dates and a suspiciously even spread of values. Real tables have a status column that is 90% one value and 1% each of five others. That skew is exactly what breaks query plans, and uniform test data never surfaces it.
• IDs are guesswork. You write customer_id = 3 in the orders insert because that's the third customer you typed. Add a row in the middle and every downstream reference is silently wrong.
• It isn't reproducible in a test. You can't say "generate 10,000 rows for this test and 2 million for the load test" — the fixture is a fixed size, so the small test never exercises the code path the big data triggers.

The fix is to stop thinking in terms of literal rows and start thinking in terms of a number sequence that you feed through expressions. That one idea unlocks everything below.

3. Number Sequences: Recursive CTEs and generate_series

Every generator in this article is driven by a table of integers. You build it once, and then every INSERT ... SELECT reads from it and computes column values with arithmetic.

MySQL 8.0+ and MariaDB: recursive CTE

SET SESSION cte_max_recursion_depth = 1000000;

WITH RECURSIVE seq (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT n FROM seq;

The first line is not optional. MySQL's default cte_max_recursion_depth is 1000, so the query above without the SET fails with error 3636, "Recursive query aborted after 1001 iterations". Raising it to a million lets the CTE run, but a recursive CTE that walks a million iterations one row at a time is the slow way to build a big series — use it for thousands to tens of thousands of rows, and switch to the cross-join numbers table below for millions.

PostgreSQL: generate_series

SELECT generate_series(1, 100000) AS n;

Postgres has this built in, it streams without materializing anything, and there is no recursion limit to raise. If you are on Postgres, generate_series is the whole answer and you can skip the rest of this section.

A portable numbers table for MySQL, SQL Server, Oracle, and SQLite

When you need millions of rows and no generate_series, build a reusable numbers table from a ten-row seed and a six-way cross join. Six digits positions give you 10⁶ = 1,000,000 rows; add a seventh join for ten million.

CREATE TABLE digits (d INT NOT NULL);
INSERT INTO digits (d) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

CREATE TABLE numbers (n INT NOT NULL PRIMARY KEY);
INSERT INTO numbers (n)
SELECT a.d
     + b.d * 10
     + c.d * 100
     + d.d * 1000
     + e.d * 10000
     + f.d * 100000
     + 1
FROM digits a
CROSS JOIN digits b
CROSS JOIN digits c
CROSS JOIN digits d
CROSS JOIN digits e
CROSS JOIN digits f;

SELECT COUNT(*) FROM numbers;   -- 1000000

That insert finishes in a few seconds on a laptop, and the resulting table is a permanent, indexable sequence you can JOIN against from any generator for the rest of the project. Because the table is materialized and primary-keyed, filtering it with WHERE n <= 50000 uses the index instead of walking rows. This one table replaces every recursive CTE you would otherwise write, and it works identically in SQL Server, Oracle, and SQLite.

4. Random Names, Emails, and Dates

Random functions are what make generated data look real. The syntax differs enough between engines to bite you, so here is the honest comparison.

The SQL Server row is the dangerous one. SELECT RAND() FROM customers returns the same number for every row, because RAND() is evaluated once before the scan. Use ABS(CHECKSUM(NEWID())) when you need a fresh value per row there. MySQL and PostgreSQL evaluate their random functions per row, so a plain SELECT RAND() does what you expect.

Both MySQL's RAND() and Postgres's random() return a float in the half-open range [0, 1) — it can return 0 but never 1. To get an integer in an inclusive range, use FLOOR(min + RAND() * (max - min + 1)). Getting the + 1 wrong is the single most common bug in generated test data; you end up with ages that never reach 80 or dates that miss the last day of the window.

-- MySQL
SELECT RAND()                                             AS r,          -- 0.0 <= r < 1.0
       FLOOR(18 + RAND() * 63)                            AS age,        -- 18..80
       DATE_ADD('2024-01-01', INTERVAL FLOOR(RAND() * 730) DAY) AS created_at;

-- PostgreSQL
SELECT random()                                           AS r,
       FLOOR(18 + random() * 63)::int                     AS age,
       DATE '2024-01-01' + (random() * 730)::int          AS created_at;

Why RAND() is the wrong tool at volume

For a few thousand rows, random functions are fine. For a million, prefer modular arithmetic over the sequence. It is faster (no per-row PRNG call), it is reproducible (re-running gives byte-identical data, which makes failing tests reproducible), and the value space is explicit instead of emergent.

INSERT INTO customers (first_name, last_name, email, created_at)
SELECT
  ELT(1 + (n % 8), 'Alice','Bob','Carol','Dave','Erin','Frank','Grace','Heidi') AS first_name,
  ELT(1 + (n % 6), 'Nguyen','Smith','Garcia','Khan','Okafor','Rossi')           AS last_name,
  CONCAT('user', n, '@example.com')                                             AS email,
  DATE_ADD('2024-01-01', INTERVAL (n * 37) % 730 DAY)                           AS created_at
FROM numbers
WHERE n <= 50000;

ELT(N, ...) is MySQL's 1-indexed list picker; 1 + (n % 8) keeps the index inside the list. The email address is unique for free because the sequence number sits in the local part — no collision handling, no UNIQUE violation after five minutes of inserting. The multiply-then-modulo on the date ((n * 37) % 730) spreads dates across two years instead of clustering them on Mondays the way n % 730 alone would.

PostgreSQL equivalent, using an array index instead of ELT:

-- PostgreSQL: arrays are 1-indexed, same trick
SELECT (ARRAY['Alice','Bob','Carol','Dave','Erin'])[1 + (n % 5)] AS first_name,
       (ARRAY['Nguyen','Smith','Garcia','Khan'])[1 + (n % 4)]   AS last_name,
       'user' || n || '@example.com'                            AS email,
       DATE '2024-01-01' + ((n * 37) % 730)                     AS created_at
FROM generate_series(1, 50000) AS n;

Mix the two approaches when you want both: deterministic keys and dates (so tests are stable) plus a random-looking numeric column like total. Reproducible structure, random noise.

5. Generating Related Rows Across Tables

Single-table data is easy. The moment you add a foreign key, you have to guarantee that every child row points at a parent that exists — otherwise your test suite dies on a constraint violation instead of the bug you were hunting.

The trick is to populate the parent key space first, then derive child foreign keys with arithmetic that cannot leave that range. If customers have ids 1 through 10,000, then 1 + (n % 10000) is always between 1 and 10,000. There is no lookup and no possibility of an orphan.

-- 1. Customers with explicit ids 1..10000
INSERT INTO customers (id, first_name, email, created_at)
SELECT n,
       ELT(1 + (n % 8), 'Alice','Bob','Carol','Dave','Erin','Frank','Grace','Heidi'),
       CONCAT('user', n, '@example.com'),
       DATE_ADD('2024-01-01', INTERVAL (n * 37) % 730 DAY)
FROM numbers
WHERE n <= 10000;

-- 2. Orders whose foreign key is guaranteed to resolve
INSERT INTO orders (customer_id, order_date, total)
SELECT
  1 + (n % 10000)                                            AS customer_id,  -- always 1..10000
  DATE_ADD('2024-01-01', INTERVAL (n * 13) % 730 DAY)        AS order_date,
  ROUND(5 + (n % 400) * 1.37, 2)                             AS total
FROM numbers
WHERE n <= 200000;

Note the difference between 1 + (n % 10000) and FLOOR(1 + RAND() * 10000). Both stay in range, but modulo gives every customer exactly 20 orders, while the random version gives a Poisson-like spread where some customers have 40 and some have 8. Which you want depends on the test: modulo for predictable, evenly-distributed loads; random for realism when you're testing "top 10 customers" queries that need a genuine long tail.

You can deliberately create skew, which is far more useful than uniform noise. A table where 90% of orders are 'completed' and 10% are spread across four other statuses mirrors production and exposes index and plan problems that uniform data hides:

-- 90% completed, 2.5% each of the rest
INSERT INTO orders (customer_id, status, total)
SELECT
  1 + (n % 10000),
  CASE
    WHEN n % 100 < 90 THEN 'completed'
    WHEN n % 100 < 92 THEN 'pending'
    WHEN n % 100 < 95 THEN 'shipped'
    WHEN n % 100 < 98 THEN 'cancelled'
    ELSE 'refunded'
  END,
  ROUND(5 + (n % 400) * 1.37, 2)
FROM numbers
WHERE n <= 200000;

Immediately after loading, prove the foreign keys actually resolve. This query must return zero, and running it takes a second:

SELECT COUNT(*) AS orphaned_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;   -- must be 0

Two other cross-table patterns are worth knowing. Many-to-many junctions are just two independent modulo keys — 1 + (n % 10000) for customer_id and 1 + (n % 500) for product_id — with a composite primary key on the pair. For nullable relationships, use CASE WHEN n % 5 = 0 THEN NULL ELSE 1 + (n % 10000) END so you actually exercise the LEFT JOIN path in your application; every generated row having a parent means that branch never runs.

6. Batch INSERT vs Row-by-Row

How you insert matters more than what you insert. The two extremes:

-- Slow: 200,000 statements, 200,000 round-trips
INSERT INTO orders (customer_id, total) VALUES (1, 10.00);
INSERT INTO orders (customer_id, total) VALUES (1, 10.00);
-- ...199,998 more

-- Fast: one statement, 200,000 rows, one round-trip
INSERT INTO orders (customer_id, total)
SELECT 1 + (n % 10000), ROUND(5 + (n % 400) * 1.37, 2)
FROM numbers
WHERE n <= 200000;

The gap is not a constant factor, it's orders of magnitude, and it comes from three costs that the single-statement version pays once instead of 200,000 times:

• Client round-trips. Each individual INSERT is a network exchange plus a parse. At a realistic 1 ms per round-trip, 200,000 inserts spend over three minutes just in latency — before the server does anything.
• Transactions and durability. Each autocommitted insert is its own transaction. With innodb_flush_log_at_trx_commit = 1, that's a redo-log flush to disk per row. Set-based inserts pay that cost once per statement.
• Index maintenance. Inserting rows in primary-key order (which the numbers sequence gives you) appends to the right edge of the B-tree. Random-id inserts scatter writes across every page in the index, causing page splits and a far larger working set. If you are generating UUID primary keys, expect the load to be several times slower than sequential integers — that's a real property of your schema worth discovering in testing, not production.

For a disposable dev dataset, you can turn off durability machinery around the load and restore it afterwards. Do this on a developer machine, never on anything shared:

SET SESSION sql_log_bin        = 0;   -- skip binary logging for the load
SET SESSION unique_checks      = 0;   -- trust the generated data
SET SESSION foreign_key_checks = 0;   -- re-verified by the LEFT JOIN check above

START TRANSACTION;
INSERT INTO orders (customer_id, order_date, total)
SELECT 1 + (n % 10000),
       DATE_ADD('2024-01-01', INTERVAL (n * 13) % 730 DAY),
       ROUND(5 + (n % 400) * 1.37, 2)
FROM numbers WHERE n <= 200000;
COMMIT;

SET SESSION foreign_key_checks = 1;
SET SESSION unique_checks      = 1;
SET SESSION sql_log_bin        = 1;

Two ceilings to respect. Keep any single VALUES (...),(...) statement under a few thousand tuples — beyond that the parser spends measurable time on a multi-megabyte string, and some clients refuse the request outright. And keep individual transactions to a manageable size (100k to 500k rows is a reasonable batch) so you don't build a giant undo log; chunking is exactly what the next section does.

7. Generating a Million Rows Without Melting the Server

The numbers table already gives you the sequence. The remaining work is batching it so a million-row load stays flat in memory and survivable if it fails halfway. A stored procedure loop is the portable way to chunk in MySQL:

DELIMITER $$
CREATE PROCEDURE load_orders()
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < 1000000 DO
    INSERT INTO orders (customer_id, order_date, total)
    SELECT 1 + ((n + i) % 10000),
           DATE_ADD('2024-01-01', INTERVAL (n + i) % 730 DAY),
           ROUND(5 + ((n + i) % 400) * 1.37, 2)
    FROM numbers
    WHERE n > i AND n <= i + 100000;
    SET i = i + 100000;
  END WHILE;
END$$
DELIMITER ;

CALL load_orders();

Ten batches of 100k rows. Each iteration is one transaction (assuming autocommit), the query reads the numbers table by primary-key range rather than scanning it, and if batch seven fails you know exactly where to resume. The (n + i) offset keeps the generated values varying between batches instead of repeating the same 100k rows ten times.

The rest of the million-row playbook, in rough order of payoff:

• Drop non-essential indexes before the load, recreate after. Maintaining a secondary index row-by-row during a bulk insert is frequently more expensive than the insert itself. Keep the child-side foreign-key index if the constraint is enforced; drop secondary indexes you added for queries and rebuild them with a single CREATE INDEX at the end.
• Use INSERT ... SELECT, not a literal list. A million-row VALUES clause is a multi-hundred-megabyte string. The set-based form never materializes it.
• On PostgreSQL, prefer COPY for the initial load. COPY orders FROM STDIN skips most per-row overhead and is the fastest path for bulk data. Generate the stream with generate_series in a COPY ... TO, or pipe it from the data generator.
• On MySQL past ~5M rows, consider LOAD DATA INFILE. It still beats set-based inserts at that size, at the cost of writing the file to disk and needing local_infile enabled.
• Raise innodb_redo_log_capacity and consider innodb_flush_log_at_trx_commit = 2 for the duration of the load, then restore them. On a dev box this can halve wall-clock time.
• Run ANALYZE TABLE when you finish. The optimizer decides whether to use your indexes based on statistics that are now badly out of date. Skip this and every EXPLAIN you run afterwards is misleading — the table looks nearly empty to the planner.

8. Verifying Distribution with EXPLAIN and GROUP BY

Generated data is only useful if it's shaped the way you intended. A generator with a bug — an off-by-one range, a modulo that clusters every value, a CASE that never matches two of its branches — produces a database that makes your queries look fine while hiding the problem. Two checks catch nearly all of it.

First, confirm the distribution. This is the step people skip, and it is the one that explains why a query was fast in testing and slow in production:

SELECT status, COUNT(*) AS row_count
FROM orders
GROUP BY status
ORDER BY row_count DESC;

If you generated five statuses, you should see five rows with a ratio matching your CASE — roughly 180,000 completed, 5,000 each of the rest on a 200k-row table. Seeing four rows, or one status swallowing everything, means the generation logic is wrong. It's also worth checking date spread (GROUP BY DATE(order_date)) and numeric range (SELECT MIN(total), MAX(total), AVG(total) FROM orders) — an average that lands exactly on your midpoint formula rather than resembling a real order value is a sign the data is too regular.

Second, refresh statistics and read the plan. This is where generated test data earns its keep: it lets you confirm the optimizer actually chooses the index at volume, before production does it for you.

ANALYZE TABLE orders;

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

With 200,000 orders spread evenly across 10,000 customers, you should see type=ref, key set to your customer index, and rows near 20. If instead you get type=ALL with key=NULL and rows close to 200,000, either the index is missing, the statistics are stale, or the column type doesn't match the literal (a common trap when customer_id is BIGINT and you compare against a string). Fix the plan now, while the data is disposable.

The high-cardinality case is the interesting one. Once you load a million rows, check a range predicate too:

EXPLAIN SELECT * FROM orders
WHERE order_date >= '2025-01-01'
  AND total BETWEEN 100 AND 500;

On skewed status data, an index on status is often correctly ignored — the planner knows the value matches most of the table and a scan is cheaper. That's a real result worth learning from generated data rather than discovering under load. On MySQL 8 you can give the planner a histogram for the low-cardinality or unevenly-distributed columns so these estimates tighten up:

ANALYZE TABLE orders UPDATE HISTOGRAM ON total, status WITH 100 BUCKETS;

None of this is a substitute for a production-shaped sample when the stakes are high. But it is dramatically better than the alternative most teams end up with — a test database carrying a few thousand uniform rows, quietly certifying queries that will behave nothing like that at scale.

FAQ — Generating Test Data in SQL