How to repeat insert statement n times incrementing a value?

Question

I have this SQL statement:

INSERT INTO `results` (batch_id, workstation_id, result, received, log_time) 
VALUES (281, 13, ABS(RANDOM()) % (232.91 - 69.87) + 69.87,
        DATETIME('now', '+1 day', 'localtime') ,
        DATETIME('now', 'localtime'));

I would like to execute this statement n times for example 30 by incrementing the number of days.

Is it possible to do this directly in SQL?

Is it possible to do this even without increasing the number of days?

Thanks for reading.

Answer 1

With a recursive CTE you can get a resultset with 30 rows which you can use in the INSERT statement:

WITH cte(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM cte WHERE n < 30)
INSERT INTO `results` (batch_id, workstation_id, result, received, log_time) 
SELECT 281, 
       13, 
       ABS(RANDOM()) % (232.91 - 69.87) + 69.87,
       DATETIME('now', n || ' day', 'localtime'),
       DATETIME('now', 'localtime')
FROM cte;

See the demo.