How to insert random data into a SQLite table using only queries?

Question

I have a test SQLite table:

CREATE TABLE test (
  id INTEGER PRIMARY KEY NOT NULL, 
  x REAL NOT NULL  DEFAULT (RANDOM()),
  y REAL NOT NULL  DEFAULT (RANDOM()),
  z REAL NOT NULL  DEFAULT (RANDOM())
)

and I want to create over thousands of rows of random data, and how can I do it using a SQL query? I can't use a SQL FOR loop because loops are not supported in SQLite, and I'm thinking of SELECTing all the data from the table then INSERT for each row, so the number of rows double each time I execute the query. I think that requires a nested query, which I don't understand.

Answer 1

You can use a recursive query.

This query generates a table with 1000 random numbers:

CREATE TABLE test(field1);

INSERT INTO test
  WITH RECURSIVE
    cte(x) AS (
       SELECT random()
       UNION ALL
       SELECT random()
         FROM cte
        LIMIT 1000
  )
SELECT x FROM cte;