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
– BWS, Commented Nov 17, 2014 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
– jessier3, Commented Nov 17, 2014 at 22:06

Lily.He · Accepted Answer · 2016-04-19 09:31:33Z

51

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, 2016 at 9:31

Lily.He

5114 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tony Babarino Over a year ago

While this code snippet may solve the question, including an explanation 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.

Community · Accepted Answer · 2017-05-23 12:09:39Z

46

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, 2017 at 12:09

CommunityBot

11 silver badge

answered Nov 17, 2014 at 22:17

Ruud Helderman

11.1k1 gold badge28 silver badges49 bronze badges

8 Comments

jessier3 Over a year ago

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.

Ruud Helderman Over a year ago

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

jessier3 Over a year ago

This has been a while ago and I forgot to come back and comment. This worked. Thank you for your help.

Megachip Over a year ago

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 :/

Ruud Helderman Over a year ago

@Megachip Please check your SQL statement with a hex viewer; there may be some invisible Unicode character in there.

|

Francisco Vargas · Accepted Answer · 2025-10-21 10:23:40Z

0

This worked for me, inspired in Ruud Helderman Answer and this geeksforgeeks post:

DROP PROCEDURE IF EXISTS myproc;
DELIMITER $$
CREATE PROCEDURE myproc()
BEGIN
    DECLARE i int DEFAULT 1;
    WHILE i <= 415 DO
    INSERT INTO table_name (Id, ModuleName, MaterialId) VALUES (NULL, 'test', i);
        SET i = i + 1;
    END WHILE;
END $$
DELIMITER ;

CALL myproc();

answered Oct 21 at 10:23

Francisco Vargas

13 bronze badges

Collectives™ on Stack Overflow

MySQL Insert with While Loop

3 Answers 3

1 Comment

8 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related