SQL Test Data Generator: Server-Side Row Generation With generate\\_series
PostgreSQL's `generate_series` lets a single `INSERT ... SELECT` populate a million rows without an application layer, and `setseed()` makes that statement produce byte-identical rows on every reload. This guide shows how to drive the insert off a parent table so foreign keys are satisfied rather than guessed, and how the approach translates to SQL Server and MySQL.
SQL primitives confirmed
Server-side generation with generate_series and setseed
SQL Server and MySQL: recursive CTE replacements
Both engines replace generate_series with a recursive CTE. In T-SQL: WITH cte AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n < 1000000) SELECT ... FROM cte OPTION (MAXRECURSION 0). The MAXRECURSION hint overrides the server default of 100 rows; 0 removes the limit entirely but should not be left in production code (Microsoft Learn WITH CTE, retrieved 2026-08-01).
In MySQL 8.4: WITH RECURSIVE cte AS (...) plus SET cte_max_recursion_depth = 1000000 to raise the 1000-row default for the session (MySQL 8.4 WITH docs; Percona Community comparison with PostgreSQL generate_series, retrieved 2026-08-01).
Runnable code sample
Verified against PostgreSQL docs retrieved 2026-08-01. Parent-driven INSERT … SELECT keeps FOREIGN KEY REFERENCES honest.
-- PostgreSQL 18. Reload a deterministic order_line fixture that respects the FK to customer.
CREATE TABLE IF NOT EXISTS order_line (
order_line_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customer (customer_id),
line_no integer NOT NULL,
quantity integer NOT NULL,
unit_price numeric(10, 2) NOT NULL,
placed_at timestamptz NOT NULL
);
TRUNCATE order_line RESTART IDENTITY;
-- Same seed, same fixture, every reload.
SELECT setseed(0.42);
INSERT INTO order_line (customer_id, line_no, quantity, unit_price, placed_at)
SELECT
c.customer_id,
line.line_no,
random(1, 8),
round(random(500, 25000)::numeric / 100, 2),
'2026-01-01 00:00:00+00'::timestamptz + (random() * INTERVAL '180 days')
FROM customer AS c
CROSS JOIN LATERAL generate_series(1, random(1, 4)) AS line(line_no);When to use Generate-Data instead
Generate-Data does not export SQL INSERT statements. The honest workflow is: generate a downloadable file, then load it with COPY / \copy /LOAD DATA INFILE (or keep using server-side SQL when the fixture must stay inside the database).
Use in-database SQL when fixtures must live next to the schema under test. Use the free generator when you need a file export (CSV for anonymous use; signed-in formats include csv, json, xml, parquet, xlsx, jsonl, hf-datasets), labeled duplicates with Master ID / Duplicate Type, or analytics-friendly Parquet before a COPY load. Anonymous use is capped at 100 rows, 6 fields, 3 exports, and CSV only. See the generator comparison and export formats guide.
Frequently asked questions
How do I insert a million rows without an external tool?
In PostgreSQL, `generate_series(1, 1000000)` inside an `INSERT ... SELECT` gives you a million row numbers in a single statement; join it to any value-generating expression and you have a million populated rows without touching an application layer. The how2.sh walkthrough (https://how2.sh/posts/how-to-generate-test-data-in-postgresql/, observed 2026-08-01) shows the basic form. The Stack Overflow thread (https://stackoverflow.com/questions/59169855/inserting-1-million-random-data-into-postgresql, observed 2026-08-01) documents the `pg_stat_activity` and timing considerations for large inserts.
What replaces `generate_series` on SQL Server or MySQL?
Both engines use a recursive CTE. In T-SQL: `WITH cte AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM cte WHERE n < 1000000) SELECT ... FROM cte OPTION (MAXRECURSION 0)`. The `MAXRECURSION` hint overrides the server default of 100 rows; 0 removes the limit entirely but should not be left in production code. In MySQL 8.4: `WITH RECURSIVE cte AS (...)` with `SET cte_max_recursion_depth = 1000000` to raise the 1000-row default for the session. The Percona Community post (https://percona.community/blog/2023/03/30/how-to-generate-test-data-for-your-database-with-sql/, retrieved 2026-08-01) puts the SQL:1999 recursive form and the PostgreSQL `generate_series` form side by side; the Microsoft Learn page at https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver17 (retrieved 2026-08-01) and the MySQL 8.4 manual at https://dev.mysql.com/doc/refman/8.4/en/with.html (retrieved 2026-08-01) confirm the depth limit and guard syntax per engine.
How do I get the same random data on every reload?
Call `SELECT setseed(n)` before your `INSERT` statement, where `n` is a float between -1 and 1. The Percona Community post notes that "many DBMSs and libraries allow you to set the initial value (seed) of the random generator" (https://percona.community/blog/2023/03/30/how-to-generate-test-data-for-your-database-with-sql/, retrieved 2026-08-01). For PostgreSQL, `setseed()` seeds subsequent `random()` calls for the remainder of the session; re-issuing the same argument at the start of any re-run produces byte-identical results (https://www.postgresql.org/docs/current/functions-math.html, retrieved 2026-08-01). Pair it with `TRUNCATE ... RESTART IDENTITY` before the insert to also reset the identity column, so every reload starts from row 1.