sql - How to copy a row n times in sqlite?

Question

For a load test I have to copy a row in my table in a sqlite database 1000 (5000, 10000) times. With the statement

INSERT INTO MYTABLE (                      
                          created,
                          modified,
                          anotherfield,
                          etc
                      )
                      SELECT created,
                          modified,
                          anotherfield,
                          etc FROM MYTABLE WHERE id = 1;

I can copy it one time. But it would be great to be able to put this into a loop to execute this statement n times. It seems like SQLite does not support for-loops. I found something called WITH RECURSIVE which could be something like the SQLite way to handle loops. But if I execute

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000)
  <insert_statement_from_above>

the insert statement gets executed only once. What am I doing wrong? How can I get to insert 1000 (5000, 10000) rows without having to add them all one by one? Thanks!

question from:https://stackoverflow.com/questions/65916622/how-to-copy-a-row-n-times-in-sqlite

Answer 1

You must CROSS join the table to the recursive cte to produce 1000 rows:

WITH RECURSIVE cte(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM cte WHERE x < 1000)
INSERT INTO MYTABLE (created, modified, anotherfield)
SELECT m.created, m.modified, m.anotherfield 
FROM MYTABLE m CROSS JOIN cte c 
WHERE m.id = 1;

See the demo (for 3 rows).

Another way to use the recursive cte:

WITH RECURSIVE cte AS (
  SELECT created, modified, anotherfield, 1 x  
  FROM MYTABLE 
  WHERE id = 1
  UNION ALL 
  SELECT created, modified, anotherfield, x + 1 
  FROM cte 
  WHERE x < 1000
)
INSERT INTO MYTABLE (created, modified, anotherfield)
SELECT created, modified, anotherfield 
FROM cte;

See the demo.