MySQL procedure to insert multiple rows

Question

I'm trying to write a procedure in MySQL to execute 30 (or more) inserts into a table. The table has other fields such ID etc, but ID is an auto_increment field and the others are not important.

This is where I got so far looking at other StackOverflow questions:

DROP PROCEDURE IF EXISTS test;
DELIMITER #
CREATE PROCEDURE test()
BEGIN

DECLARE i INT UNSIGNED DEFAULT 1;

WHILE i < 30
    INSERT INTO account (login, password)
    SELECT concat('test', i), '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

    SET i = i + 1;
END WHILE;
COMMIT; 
END #

DELIMITER;

CALL test();

But MySQL returns the error 1064 "Syntax error on line 7, check your manual". Line 7 is the one that begins with WHILE.

I also tried with this INSERT statement:

INSERT INTO account (login, password)
VALUES (concat('test', i), '*00A51F3F48415C7D4E8908980D443C29C69B60C9');

But without any luck, MySQL gives back the same error.

Is there something I'm missing? Or this is something that just can't be accomplished?

Thanks in advance.

score 3 · Answer 1 · answered Jul 20 '14 at 15:14

The keyword DO is missing:

WHILE Syntax
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]

So your SQL should be:

DROP PROCEDURE IF EXISTS test;
DELIMITER #
CREATE PROCEDURE test()
BEGIN

DECLARE i INT UNSIGNED DEFAULT 1;

-- next line needs a DO at the end
WHILE i < 30 DO                    
    INSERT INTO account (login, password)
    SELECT concat('test', i), '*00A51F3F48415C7D4E8908980D443C29C69B60C9';

    SET i = i + 1;
END WHILE;
COMMIT; 
END #

DELIMITER;

MySQL procedure to insert multiple rows

1 Answers1