insert multiple records in my mySQL table

Question

I wanna insert multiple records in my mySQL table:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (1, 'Name 1', 'Value 1', 'Other 1'),
  (2, 'Name 2', 'Value 2', 'Other 2'),
  (3, 'Name 3', 'Value 3', 'Other 3'),
  (4, 'Name 4', 'Value 4', 'Other 4');
...

Can I do that with a loop? something like that:

for (i=1 ; i<100 ; i++)
INSERT INTO example
      (example_id, name, value, other_value)
    VALUES
      (i, 'Name '+i, 'Value '+i, 'Other '+i);

possible duplicate of [For loop example in MySQL](http://stackoverflow.com/questions/5125096/for-loop-example-in-mysql) — AeroX, Jul 15 '14 at 13:31

vicentazo · Accepted Answer · 2014-07-15T14:02:41.173

0

You can create a stored routine in Mysql (http://dev.mysql.com/doc/refman/5.0/es/create-procedure.html) that performs that loop. For example:

DELIMITER //
CREATE PROCEDURE test()
BEGIN
    DECLARE CONT INT;
    DECLARE END_VALUE INT;

    SET CONT = 1;
    SET END_VALUE = 100;

    WHILE CONT < END_VALUE DO
        INSERT INTO example_vrc(example_id, name, value, other_value)
        VALUES(CONT, CONCAT('Name ', CONT), CONCAT('Value ', CONT), CONCAT('Other ', CONT));

        SET CONT = CONT + 1;
    END WHILE;

END;//
DELIMITER ;

After you have loaded the procedure into the database, you will be able to execute through:

CALL test();

edited Jul 15 '14 at 14:02

answered Jul 15 '14 at 13:32

vicentazo

1,739
15
19

-1 because it doesn't answer the question. Also the solution is too complex, instead of using prepared statements (which are made for such purposes), you opt for a procedure - without even supplying parameters for insertion. – N.B. Jul 15 '14 at 14:10

insert multiple records in my mySQL table

1 Answers1