MySQL : Running a loop without procedure/function in SQL file

Question

I need to insert bulk data into a table for testing purpose via MySQL script file data.sql. But I want avoid dumping bulk insert statements into the file. So thought of using a loop, but without using procedure / function. Somewhat similar to this , here OP wants to achieve this in a Editor and I am looking achieve it using script file.

So i tried to import a script file containing below code, but ended up in error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE (i <= 100) DO INSERT INTO

use test_db;
 BEGIN
   DECLARE int_val INT DEFAULT 0;
     test_loop : LOOP
      IF (int_val = 10) THEN
       LEAVE test_loop;
      END IF;

      INSERT INTO `test_db`.`ztest_states`
      (`city`,`state`,`zip`) VALUES ( 'XXX', 'YYY', '11111');

      SET int_val = int_val +1;
      SELECT int_val; 
    END LOOP; 
 END$$

Please suggest a ways to achieve what I am looking for or better solution. Thanks for your time.

MySQL does not support anonymous code blocks. – Akina Jun 25 '20 at 08:23 — Akina, Jun 25 '20 at 08:23

Akina · Accepted Answer · 2020-06-25T11:46:32.400

INSERT INTO `test_db`.`ztest_states` (`city`,`state`,`zip`) 
SELECT 'XXX', 'YYY', '11111'
FROM ( SELECT 0 UNION SELECT 1 UNION .. UNION SELECT 9 ) numbers;

or, in MySQL 8+

INSERT INTO `test_db`.`ztest_states` (`city`,`state`,`zip`) 
WITH RECURSIVE
cte AS ( SELECT 0 num 
         UNION ALL 
         SELECT num+1 FROM cte WHERE num < 9 )
SELECT 'XXX', 'YYY', '11111'
FROM cte;

The solution insert 10 records with same data. Instead can we insert like 'xxx-01', 'yyy-01','11111-01', 'xxx-02', 'yyy-02','11111-02',.. and so on?

INSERT INTO `test_db`.`ztest_states` (`city`,`state`,`zip`) 
WITH RECURSIVE
cte AS ( SELECT 0 num 
         UNION ALL 
         SELECT num+1 FROM cte WHERE num < 9 )
SELECT CONCAT('XXX-', LPAD(num, 2, '0'),
       CONCAT('YYY-', LPAD(num, 2, '0'),
       CONCAT('11111-', LPAD(num, 2, '0')
FROM cte;

Adjust starting (now 0) and finalizing (now 9) values, and left-padding length.

What happens here?

Read Recursive Common Table Expressions. It explains everything.

Thanks you.. 2nd solution works. Can we set dynamic value instead static values like 'XXX', 'YYY', '11111'? And also if you could add brief description about solutions, will be very helpful. — Sameer K, Jun 25 '20 at 11:18

MySQL : Running a loop without procedure/function in SQL file

1 Answers1

Linked