How to fill a table with test data: from hand-written INSERTs to generating rows with SQL

Almost every DBA I know was a developer first, or still is. I was writing code before I moved into databases: I started the first version of Calíope in 2008, and I have worked as a DBA since 2016. From those early years as a developer I remember a problem with nothing sophisticated about it, one we have all had: you create a new table and you need data in it to see the application working.

This post goes through the ways I solved it, in the order I learned them. The examples are written in MySQL, and every code block says which engines it runs on as is.

They all use this table:

CREATE TABLE clientes (
    id     INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100)  NOT NULL,
    email  VARCHAR(150)  NOT NULL,
    saldo  DECIMAL(10,2) NOT NULL DEFAULT 0,
    alta   DATE          NOT NULL
);

On the other engines the only thing that changes is the auto-increment: GENERATED ALWAYS AS IDENTITY on PostgreSQL and Oracle, IDENTITY(1,1) on SQL Server and INTEGER PRIMARY KEY on SQLite. ClickHouse has no auto-increment.

1. One INSERT after another

At the beginning, out of inexperience and because I didn't know there was another way, I typed the data in by hand. One INSERT, then another, then another:

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Ana Mora', 'ana@ejemplo.com', 150.00, '2010-03-01');

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Luis Rojas', 'luis@ejemplo.com', 80.50, '2010-03-02');

INSERT INTO clientes (nombre, email, saldo, alta)
VALUES ('Sofía Vargas', 'sofia@ejemplo.com', 0.00, '2010-03-05');

On Oracle the date is written DATE '2010-03-01': a bare string is read with the session's date format, and with the default one it fails.

It works, but it has two problems. Writing more than a handful of rows takes a lot of effort, and the data is always the same. With three customers you don't notice that paging is broken, that the list is slow or that a long name breaks the screen.

2. The table that copies itself

Later I found out that an INSERT doesn't need VALUES: it can be followed by a SELECT. And if the SELECT reads from the same table you are inserting into, the table doubles:

INSERT INTO clientes (nombre, email, saldo, alta)
SELECT nombre, email, saldo, alta
FROM clientes;

Each run doubles the rows. The first three become 6, then 12, 24… and after 18 runs there are already 786,432. The id column is not in the list, so the engine numbers the copies.

The problem is that the data is still the same three rows repeated, and if email has a unique index the second run fails. The fix is to vary the values in the copy itself:

INSERT INTO clientes (nombre, email, saldo, alta)
SELECT CONCAT(nombre, ' ', id),
       CONCAT(id, '.', email),
       ROUND(RAND() * 1000, 2),
       alta + INTERVAL FLOOR(RAND() * 365) DAY
FROM clientes;

On other engines the random function and the date arithmetic change:

• PostgreSQL: random() and alta + (random() * 365)::int.
• SQLite: abs(random()) % 1000 and date(alta, '+' || (abs(random()) % 365) || ' days').
• SQL Server: RAND() returns the same value for every row of the statement. To get a different value per row, use ABS(CHECKSUM(NEWID())) % 1000.
• Oracle: DBMS_RANDOM.VALUE(0, 1000).
• ClickHouse: rand() % 1000.

A warning: the growth is exponential, and so is what the transaction log writes. Two runs too many and the table is four times bigger.

3. Generating the rows with SQL

Over the years I learned that you don't need to start from rows that already exist: the engine itself can generate a series of numbers, and each number becomes a row. In MySQL you do it with a recursive CTE:

SET SESSION cte_max_recursion_depth = 100000;

INSERT INTO clientes (nombre, email, saldo, alta)
WITH RECURSIVE serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT CONCAT('Cliente ', n),
       CONCAT('cliente', n, '@ejemplo.com'),
       ROUND(RAND() * 10000, 2),
       DATE '2020-01-01' + INTERVAL FLOOR(RAND() * 2000) DAY
FROM serie;

The first line is needed because MySQL stops recursion at 1000 levels by default.

Each engine has its own way of generating the series, and on some it is much shorter:

SELECT seq FROM seq_1_to_100000;

SELECT n FROM generate_series(1, 100000) AS n;

SELECT value FROM GENERATE_SERIES(1, 100000);

SELECT LEVEL FROM dual CONNECT BY LEVEL <= 100000;

SELECT number + 1 FROM numbers(100000);

On SQLite, and on SQL Server before 2022, you use the recursive CTE:

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

SQL Server doesn't take RECURSIVE, and it stops recursion at 100 levels. An option at the end removes the limit:

WITH serie (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM serie WHERE n < 100000
)
SELECT n FROM serie
OPTION (MAXRECURSION 0);

Since when has this been possible?

In 2008 I didn't know this existed, and in MySQL, which is what I was using, it actually didn't. This is the timeline, from oldest to newest:

• PostgreSQL 8.0, January 2005: generate_series. Recursive CTEs came later, in 8.4 (2009).
• SQL Server 2005, November 2005: recursive CTEs. GENERATE_SERIES didn't arrive until SQL Server 2022.
• Oracle: CONNECT BY LEVEL on dual was already used as a row generator in Oracle 10g (2004). The standard recursive CTE arrived in 11g R2 (2009).
• SQLite 3.8.3, February 2014: WITH RECURSIVE.
• MariaDB 10.0 (2014): the Sequence engine and its seq_1_to_N tables. Recursive CTEs came in MariaDB 10.2 (2017).
• ClickHouse: numbers() has been there since it was released as open source, in 2016.
• MySQL 8.0, April 2018: recursive CTEs. Before that there was no way to generate a series in a single statement.

In other words, in 2008 PostgreSQL, SQL Server and Oracle could already do it. MySQL took ten more years, and in the meantime what was left was doubling the table, or a procedure with a loop.

4. A procedure that reads the table

Once I had become a bit more professional, I wrote a stored procedure that did that work for me: it read the table's columns and their types from information_schema, and built an INSERT with a suitable random value for each one.

DELIMITER //
CREATE PROCEDURE poblar(IN p_tabla VARCHAR(64), IN p_filas INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE v_columnas TEXT;
    DECLARE v_valores  TEXT;

    SELECT GROUP_CONCAT(CONCAT('`', column_name, '`') ORDER BY ordinal_position),
           GROUP_CONCAT(
               CASE
                   WHEN data_type IN ('int', 'bigint', 'smallint', 'tinyint')
                       THEN 'FLOOR(RAND() * 1000)'
                   WHEN data_type IN ('decimal', 'double', 'float')
                       THEN 'ROUND(RAND() * 1000, 2)'
                   WHEN data_type IN ('date', 'datetime', 'timestamp')
                       THEN 'CURRENT_DATE - INTERVAL FLOOR(RAND() * 2000) DAY'
                   ELSE CONCAT('LEFT(SHA2(RAND(), 256), ',
                               LEAST(IFNULL(character_maximum_length, 32), 32), ')')
               END
               ORDER BY ordinal_position)
      INTO v_columnas, v_valores
      FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = p_tabla
       AND extra NOT LIKE '%auto_increment%';

    SET @sql = CONCAT('INSERT INTO `', p_tabla, '` (', v_columnas, ') VALUES (', v_valores, ')');
    PREPARE sentencia FROM @sql;
    WHILE i < p_filas DO
        EXECUTE sentencia;
        SET i = i + 1;
    END WHILE;
    DEALLOCATE PREPARE sentencia;
END //
DELIMITER ;

CALL poblar('clientes', 1000);

The other engines have their own column catalog (information_schema on PostgreSQL and SQL Server, ALL_TAB_COLUMNS on Oracle, pragma_table_info on SQLite) and their own procedural language, so it has to be rewritten for each one.

5. Today, with Calíope

That procedure is the ancestor of a Calíope feature. In Catalogs, in the table list, a table's context menu has Populate with dummy data (right-click on the Mac, touch and hold on the iPad). You choose how many rows you want, from 1 to 5000, and Calíope:

• reads the columns and their types from the server's catalog, including the values of an ENUM;
• skips auto-increment and computed columns, which the engine fills in itself;
• generates a valid sample value for each column's type, and shows you one before it starts;
• inserts in batches of 50 rows, with a progress bar, and if a batch fails it tells you why: a value out of range, a CHECK constraint, a duplicate.

In columns that are foreign keys, the values are random too. On MySQL, MariaDB and PostgreSQL, Calíope turns the check off during the load, so you can end up with rows that point to a parent that doesn't exist, which is useful for testing how the application handles a JOIN with no match. On SQL Server and Oracle the check stays on, and a batch with a value that doesn't exist in the parent table fails with its warning.

It works on MySQL, MariaDB, PostgreSQL, SQL Server, Oracle and ClickHouse.

What I use for what

None of these approaches has disappeared from my work:

• A few INSERTs by hand when I need specific data I know, like the case that reproduces a bug.
• The series generated with SQL when I need volume and control over how the values are spread: a hundred thousand customers with 80% of the sign-ups in the last year.
• Calíope when I just want the table to have something in it to see the screen working.