How to create a 1M record table with a single query (opens in new tab)

Here's how to do something similar in Q (KDB)

    ([]x?x:1000000)

Which gives a table of a million rows like:

    x
    ------
    877095
    265141
    935540
    49015
    ...

This can generally fall under the category of “Generate Test Data in SQL” with both generic techniques like Recursive CTEs, as in the OP, and SQL dialect or tool specific options. Search is your friend. The OP also provides a table lookup technique for readable names but doesn’t address other data types such as timestamps or other functions such as MD5(). Other data distribution techniques other than random may also be needed. This post scratches the surface of testing with generated data.

If you're running PostgreSQL you can also just

  xxd -ps -c 16 -l $bignum < /dev/urandom | (echo '\copy random_data from stdin'; cat) | psql

Depending on how you want your random keys formatted.

you have to use it since PostgreSQL does not support limit within a with expression.

use a sequence or uuid

Some DBs support "literal tables" which makes this a little less nasty:

    SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n)

for instance:

        SELECT seq = units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000
          FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS units(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS tens(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS hundreds(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS thousands(n)
         WHERE units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000 BETWEEN 1 AND 1337

You can make it neater still by making the sub-table with a CTE:

    WITH Digits AS (SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n))
            SELECT seq = units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000
              FROM Digits AS units
        CROSS JOIN Digits AS tens
        CROSS JOIN Digits AS hundreds
        CROSS JOIN Digits AS thousands
             WHERE units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000 BETWEEN 1 AND 1337

or

    WITH Digits AS (SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) AS d(n))
    , Thousands AS (SELECT seq = units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000
              FROM Digits AS units
        CROSS JOIN Digits AS tens
        CROSS JOIN Digits AS hundreds
        CROSS JOIN Digits AS thousands
        )
    SELECT seq FROM thousands ORDER BY seq
     WHERE units.n + tens.n*10 + hundreds.n*100 + thousands.n*1000 BETWEEN 1 AND 1337

though at that point you probably have the recursive option so you'd need to test to see which is more efficient. As a further hack you can combine this and the recursive CTE to extend the number of rows you can return when recursion limits would otherwise be a problem.

You can also use simple window functions to make a sequence from a system table that you know will have a few thousand rows:

    SELECT n=ROW_NUMBER() OVER (ORDER BY object_id) FROM sys.all_columns

Or if you just need a few thousand rows to hang randomly generated data from you don't even need the function.

All a bit hacky compared to some DBs that have sequence support, but useful for generating test data or numbers tables where that isn't supported.

You want to run a transaction in production and you're unsure about the impact because it might take a lock and block other transactions for some time. In that case you may want to spin up a separate database with fake data and test it there first.

You could create a new database and restore a production backup instead of using fake data but that might not be allowed or require some kind of approval due to rules protecting the privacy of customers or employees.

Generating test data batches like this is a common use.

The other is for populating "numbers tables" which can be very useful in reporting contexts and/or dealing with certain gaps/islands type problems.

You can even use the techniques dynamically rather than a stored numbers table, if you need it in a DB that doesn't have one and you don't have schema permissions to create one, though this is generally less efficient.

Creating realistic fake data is useful in lower environments and for load testing. Outside of SQL I like faker: https://github.com/joke2k/faker

Exactly like that, yes.

This is how I do it. You can cross join the derived table to itself if you need more rows.

Much more performant and naturally relational way of generating data than looping recursively.

WITH RECURSIVE is supported in MariaDB 10.2+, MySQL 8.0+, PostgreSQL 8.4+ and SQLite 3.8+. Oracle 11.2+ and SQL Server 2005 support recursive queries, but without the RECURSIVE keyword.

Not sure if it's up-to date though : https://modern-sql.com/caniuse/with_recursive_(top_level)

FWIW Sybase SQL Anywhere has supported it since v10[1] at least. Current version is v17[2].

[1]: http://dcx.sap.com/index.html#1001/en/dbrfen10/rf-select-sta...

[2]: http://dcx.sap.com/index.html#sqla170/en/html/8190aea36ce210...

This is the list that i could find:

Microsoft SQL Server, Firebird 2.1, PostgreSQL, SQLite, IBM Informix version, CUBRID, MariaDB and MySQL

I sort of assumed all of them did!