SQL query how to create 9999 records with number iteration?

Question

MySQL

There is a table:


You need to query 9999 records in which the "VALUE" value will increase by 1,
i.e. it will look like this:

ID 1, DATE_CREATE (today's date and time), DATE_UPDATE (today's date and time), ACTIVE (1 ), USER_ID (1), VALUE 1

ID 2, DATE_CREATE (today's date and time), DATE_UPDATE (today's date and time), ACTIVE (1), USER_ID (1), VALUE 2

ID 3, DATE_CREATE (today's date and time) , DATE_UPDATE (today's date and time), ACTIVE (1), USER_ID (1), VALUE 3

ID 4, DATE_CREATE (today's date and time), DATE_UPDATE (today's date and time), ACTIVE (1), USER_ID (1), VALUE 4

etc.

How to do it in one query? Or do you need to execute each request 1 time, i.e. 9999 lines = 9999 requests?

Answer 1

What prevents on the "client" side (by means of the application itself) to generate SQL?

INSERT INTO `table` (`DATE_CREATE`, `DATE_UPDATE`, `ACTIVE`, `USER_ID`, `VALUE`) VALUES
(NOW(), NOW(), 1, 1, 1),
(NOW(), NOW(), 1, 1, 2),
(NOW(), NOW(), 1, 1, 3),
/*...*/
(NOW(), NOW(), 1, 1, 9999);

PS: I see from the screenshot that Bitrix... you can even make a request in a text file and execute it through the Bitrix Admin panel in the "SQL query" section... if you need it only once...

Answer 2

It depends on which version of MySQL, here MariaDB, for example, can do this

INSERT INTO table
SELECT
seq, NOW(), NOW(), 1, 1, seq
FROM seq_1_to_9999

But in general it is a task for the client side.

Answer 3

SET `cte_max_recursion_depth` = 10000;
INSERT INTO `test` (`DATE_CREATE`, `DATE_UPDATE`, `ACTIVE`, `USER_ID`, `VALUE`) 
  WITH RECURSIVE `cte` (`DATE_CREATE`, `DATE_UPDATE`, `ACTIVE`, `USER_ID`, `VALUE`) AS (
    SELECT NOW(), NOW(), 1, 1, 1 AS `VALUE`
    UNION
    SELECT NOW(), NOW(), 1, 1, `VALUE`+1 FROM `cte` WHERE `VALUE` < 9999
  )
  SELECT *
    FROM `cte`

Answer 4

1. Here it is necessary, each time to take care of setting the values ​​of the DATE_CREATE / DATE_UPDATE fields?

ALTER TABLE tablename
MODIFY COLUMN date_create TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
MODIFY COLUMN date_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Everything, now they themselves will take care of the correct value. More precisely, the server will take care of it. Unless, of course, it occurs to the code to set the values ​​of these fields explicitly (what for - just extra work).
2. To perform the operation, we use a recursive CTE (remember that the DATE_CREATE / DATE_UPDATE fields themselves take care of the correct value, just like an auto-incrementing primary key):

INSERT INTO tablename (active, user_id, value)
WITH RECURSIVE
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num + 1 FROM cte WHERE num < 9999 )
SELECT 1, 1, num
FROM cte;

You may need to first set a sufficient value for the session variable: If the MySQL version is old and does not understand CTE, thenSET SESSION cte_max_recursion_depth = 10000;

INSERT INTO tablename (active, user_id, value)
SELECT 1, 1, 1 + t1.num + t2.num * 10 + t3.num * 100 + t4.num * 1000 AS value
FROM       (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
CROSS JOIN (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4;
HAVING value <= 9999;