Cómo llenar una tabla con datos de prueba: de los INSERT a mano a generarlos con SQL

Casi todos los DBA que conozco fuimos desarrolladores antes, o lo seguimos siendo. Yo programo desde antes de dedicarme a las bases de datos: en 2008 empecé la primera versión de Calíope, y trabajo como DBA desde 2016. De aquellos primeros años como desarrollador me queda un problema que no tiene nada de sofisticado y que todos hemos tenido: creas una tabla nueva y necesitas datos dentro para ver la aplicación funcionando.

Esta entrada recorre las maneras en que lo fui resolviendo, en el orden en que las aprendí. Los ejemplos están escritos en MySQL, y cada bloque de código dice en qué motores corre tal cual.

Todos usan esta tabla:

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
);

En los demás motores lo único que cambia es el autoincremento: GENERATED ALWAYS AS IDENTITY en PostgreSQL y Oracle, IDENTITY(1,1) en SQL Server e INTEGER PRIMARY KEY en SQLite. ClickHouse no tiene autoincremento.

1. Un INSERT tras otro

Al principio, por inexperiencia y porque no sabía que hubiera otra forma, metía los datos a mano. Un INSERT, luego otro, luego otro:

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');

En Oracle la fecha se escribe DATE '2010-03-01': una cadena sola se interpreta con el formato de fecha de la sesión, y con el de omisión falla.

Funciona, pero tiene dos problemas. Cuesta mucho escribir más de un puñado de filas, y los datos son siempre los mismos. Con tres clientes no se nota que la paginación está mal, que el listado tarda o que un nombre largo rompe la pantalla.

2. La tabla que se copia a sí misma

Después descubrí que un INSERT no necesita VALUES: puede llevar un SELECT detrás. Y si el SELECT lee de la misma tabla en la que se inserta, la tabla se duplica:

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

Cada ejecución duplica las filas. Las tres del principio son 6, luego 12, 24… y con 18 ejecuciones ya son 786 432. La columna id no va en la lista, así que el motor numera las copias.

El problema es que los datos siguen siendo las mismas tres filas repetidas, y si email tiene un índice único la segunda ejecución falla. Se arregla variando los valores en la propia copia:

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;

En otros motores cambian la función aleatoria y la aritmética de fechas:

• PostgreSQL: random() y alta + (random() * 365)::int.
• SQLite: abs(random()) % 1000 y date(alta, '+' || (abs(random()) % 365) || ' days').
• SQL Server: RAND() devuelve el mismo valor en todas las filas de la sentencia. Para que cambie fila a fila se usa ABS(CHECKSUM(NEWID())) % 1000.
• Oracle: DBMS_RANDOM.VALUE(0, 1000).
• ClickHouse: rand() % 1000.

Un aviso: el crecimiento es exponencial, y lo es también lo que escribe el registro de transacciones. Dos ejecuciones de más cuadruplican la tabla.

3. Generar las filas con SQL

Con los años aprendí que no hace falta partir de filas que ya existen: el propio motor puede generar una serie de números, y de cada número sale una fila. En MySQL se hace con una CTE recursiva:

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;

La primera línea hace falta porque MySQL corta la recursión en 1000 niveles por omisión.

Cada motor tiene su propia forma de generar la serie, y en algunos es mucho más corta:

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);

En SQLite, y en SQL Server anterior a 2022, se usa la CTE recursiva:

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

SQL Server no lleva RECURSIVE, y corta la recursión en 100 niveles. El tope se quita con una opción al final:

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

¿Desde cuándo se puede?

En 2008 yo no sabía que esto existiera, y en MySQL, que era lo que usaba, de hecho no existía. Ésta es la cronología, de la más antigua a la más reciente:

• PostgreSQL 8.0, enero de 2005: generate_series. Las CTE recursivas llegaron después, en la 8.4 (2009).
• SQL Server 2005, noviembre de 2005: CTE recursivas. GENERATE_SERIES no llegó hasta SQL Server 2022.
• Oracle: CONNECT BY LEVEL sobre dual ya se usaba como generador de filas en Oracle 10g (2004). La CTE recursiva estándar llegó en 11g R2 (2009).
• SQLite 3.8.3, febrero de 2014: WITH RECURSIVE.
• MariaDB 10.0 (2014): el motor Sequence y sus tablas seq_1_to_N. Las CTE recursivas, en MariaDB 10.2 (2017).
• ClickHouse: numbers() está desde que se publicó como código abierto, en 2016.
• MySQL 8.0, abril de 2018: CTE recursivas. Antes de eso no había forma de generar una serie con una sola sentencia.

Es decir, en 2008 PostgreSQL, SQL Server y Oracle ya podían hacerlo. En MySQL hubo que esperar diez años más, y mientras tanto lo que quedaba era duplicar la tabla, o un procedimiento con un bucle.

4. Un procedimiento que lee la tabla

Cuando ya me había vuelto un poco más profesional, escribí un procedimiento almacenado que hacía ese trabajo por mí: leía las columnas de la tabla y sus tipos en information_schema, y armaba un INSERT con un valor aleatorio adecuado para cada una.

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);

Los demás motores tienen su propio catálogo de columnas (information_schema en PostgreSQL y SQL Server, ALL_TAB_COLUMNS en Oracle, pragma_table_info en SQLite) y su propio lenguaje de procedimientos, así que hay que reescribirlo para cada uno.

5. Hoy, con Calíope

Ese procedimiento es el antepasado de una función de Calíope. En Catálogos, en la lista de tablas, el menú contextual de una tabla tiene Poblar con datos dummy (clic derecho en el Mac, mantener pulsado en el iPad). Eliges cuántas filas quieres, de 1 a 5000, y Calíope:

• lee las columnas y sus tipos del catálogo del servidor, incluidos los valores de un ENUM;
• se salta las columnas autoincrementales y las calculadas, que el motor rellena solo;
• genera un valor de ejemplo válido para el tipo de cada columna, y te enseña uno antes de empezar;
• inserta en tandas de 50 filas, con una barra de progreso, y si una tanda falla te dice por qué: un valor fuera de rango, una restricción CHECK, un duplicado.

En las columnas que son clave foránea, los valores también salen al azar. En MySQL, MariaDB y PostgreSQL, Calíope apaga la comprobación durante la carga, así que pueden quedar filas que apuntan a un padre que no existe, útiles para probar cómo responde la aplicación a un JOIN sin pareja. En SQL Server y Oracle la comprobación sigue encendida, y una tanda con un valor que no existe en la tabla padre falla con su aviso.

Funciona en MySQL, MariaDB, PostgreSQL, SQL Server, Oracle y ClickHouse.

Qué uso para qué

Ninguna de estas maneras ha desaparecido de mi trabajo:

• Unos pocos INSERT a mano cuando necesito datos concretos que conozco, como el caso que reproduce un fallo.
• La serie generada con SQL cuando necesito volumen y controlar cómo se reparten los valores: cien mil clientes con el 80 % de las altas en el último año.
• Calíope cuando solo quiero que la tabla tenga algo dentro para ver la pantalla funcionando.