Q: How to insert random data into a SQLite table using only queries? 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.

A: 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;


A: I agree with Stenci, you can use a recursive query. Since SQLite Release 3.8.3 On 2014-02-03 has added support for CTEs.
The following query generates the table the OP has requested with 1000 random numbers:
CREATE TABLE test (
  id INTEGER PRIMARY KEY NOT NULL, 
  x REAL NOT NULL,
  y REAL NOT NULL,
  z REAL NOT NULL
);
INSERT INTO test
    WITH RECURSIVE
      cnt( id, x, y, z) AS (
      VALUES(1 , random(), random(), random()) UNION ALL 
      SELECT id+1,random(),random(), random() FROM cnt WHERE ID<1000)
    select * from cnt;


A: Going for hacky, this'll insert up to 2048 random rows in one go, decide how many by setting the limit;
INSERT INTO TEST (x,y,z)
  SELECT RANDOM(), RANDOM(), RANDOM()
   FROM (SELECT * FROM (
         (SELECT 0 UNION ALL SELECT 1) t2, 
         (SELECT 0 UNION ALL SELECT 1) t4,
         (SELECT 0 UNION ALL SELECT 1) t8,
         (SELECT 0 UNION ALL SELECT 1) t16,
         (SELECT 0 UNION ALL SELECT 1) t32,
         (SELECT 0 UNION ALL SELECT 1) t64,
         (SELECT 0 UNION ALL SELECT 1) t128,
         (SELECT 0 UNION ALL SELECT 1) t256,
         (SELECT 0 UNION ALL SELECT 1) t512,
         (SELECT 0 UNION ALL SELECT 1) t1024,
         (SELECT 0 UNION ALL SELECT 1) t2048
         )
    ) LIMIT 246;


A: This seems to do what you want. You would have to run it a few times since it doubles the number of existing rows each time. And you need to have at least one row of data present to begin with.
insert into test select random(), random(), random(), random() from test;

A: If you are in the sqlite3 shell, you can use generate_series():
insert into test (x,y,z) select random(), random(), random() from generate_series(1, 10000);

A: Just mix the functions RANDOM() and ABS() with the operation % (mod).
Example: insert random number from 0 to 10
INSERT INTO test(field1) VALUES( abs(random() % 11) );

