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 <= 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 <= 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 <= 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?

what is the definition of your table ... how is 'code' declared ? — BWS, Nov 17 '14 at 21:50
code is a varchar. I get the same error when I run this : BEGIN DECLARE @i VARCHAR unsigned DEFAULT 1001; WHILE (i <= 2200) DO INSERT INTO `coupon` (couponCode, active, totalUses) values (i, 1, 1); SET i = i+1; END WHILE; END — jessier3, Nov 17 '14 at 22:06

score 47 · Answer 1 · answered Apr 19 '16 at 09:31

47

drop procedure if exists doWhile;
DELIMITER //  
CREATE PROCEDURE doWhile()   
BEGIN
DECLARE i INT DEFAULT 2376921001; 
WHILE (i <= 237692200) DO
    INSERT INTO `mytable` (code, active, total) values (i, 1, 1);
    SET i = i+1;
END WHILE;
END;
//  

CALL doWhile();

answered Apr 19 '16 at 09:31

Lily.He

471
4
3

8

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Tony Babarino Apr 19 '16 at 09:58

score 42 · Accepted Answer · edited May 23 '17 at 12:09

42

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 <= 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.

edited May 23 '17 at 12:09

Community

1
1

answered Nov 17 '14 at 22:17

Ruud Helderman

10,563
1
26
45

Yeah, the numbers were provided and I didn't go in and check them. Thank you for catching that. What I am getting from your post is that there is no way to do this with SQL. That I have to pull the code out and generate each individual statement with PHP. – jessier3 Nov 17 '14 at 23:46
@jessier3: You misunderstood; it is perfectly well possible in MySQL. I added sample code and a fiddle to clarify. But feel free to use the alternative approach instead; whatever you find convenient. – Ruud Helderman Nov 18 '14 at 20:21
This has been a while ago and I forgot to come back and comment. This worked. Thank you for your help. – jessier3 Dec 01 '14 at 21:36
5

I've got an `#1064 - 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 '' at line 3` with 5.1 ... regarding the documentation this should work :/ – Megachip Jul 17 '17 at 20:34
@Megachip Please check your SQL statement with a hex viewer; there may be some invisible Unicode character in there. – Ruud Helderman Jul 21 '17 at 19:35
will you please convert the whole process in a single inline sql statement. that will be very helpful. – Sanghamitra Lahiri Oct 31 '19 at 18:58
How would you insert text in place of numbers? For e.g; first name and last name of 10 persons – Rajiv Iyer May 19 '22 at 15:23
@RajivIyer That is a different scenario. This Q&A is about inserting a range (of numbers), not about inserting a given list of literal values. This may help: https://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql – Ruud Helderman May 19 '22 at 16:33

MySQL Insert with While Loop

2 Answers2

Linked