MySQL Insert with While Loop

Question

I m trying to create a bunch of records in my MySQL database   This is a one time creation so I am not trying to create a stored procedure   Here is my code   BEGIN SET i   2376921001  WHILE  i  lt   237692200  DO     INSERT INTO  mytable   code  active  total  values  i  1  1       SET i   i 1  END WHILE  END   Here is the error    ERROR in query 1  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  SET i   2376921001 WHILE  i  lt   237692200  DO     INSERT INTO coupon  couponCod  at line 2 Execution stopped   I have tried a Declare with the same results   Code below   BEGIN DECLARE i INT unsigned DEFAULT 2376921001  WHILE  i  lt   237692200  DO     INSERT INTO  mytable   code  active  total  values  i  1  1       SET i   i 1  END WHILE  END   The one other thing I have tried is with  i instead of just i   Same error  Can anyone see what I am doing wrong

User · Answer

drop procedure if exists doWhile  DELIMITER      CREATE PROCEDURE doWhile      BEGIN DECLARE i INT DEFAULT 2376921001   WHILE  i  lt   237692200  DO     INSERT INTO  mytable   code  active  total  values  i  1  1       SET i   i 1  END WHILE  END        CALL doWhile

User · Answer

You cannot use WHILE like that  see  mysql DECLARE WHILE outside stored procedure how   You have to put your code in a stored procedure  Example   CREATE PROCEDURE myproc   BEGIN     DECLARE i int DEFAULT 237692001      WHILE i  lt   237692004 DO         INSERT INTO mytable  code  active  total  VALUES  i  1  1           SET i   i   1      END WHILE  END   Fiddle  http   sqlfiddle com   2 a4f92 1  Alternatively  generate a list of INSERT statements using any programming language you like  for a one-time creation  it should be fine  As an example  here s a Bash one-liner   for i in  2376921001  2376921099   do echo  INSERT INTO mytable  code  active  total  VALUES   i  1  1     done   By the way  you made a typo in your numbers  2376921001 has 10 digits  237692200 only 9

[mysql] MySQL Insert with While Loop

Examples related to mysql

Examples related to while-loop