Casey.PhillipsTMC Casey.PhillipsTMC - 1 year ago 60
MySQL Question

Using MySQL to INSERT a value into a single table n times

I have been searching and searching via google, so apologies if this has already been answered or is very simple.

I am working on a MySQL project where I need to add the same value multiple times. For instance, the following code is adding one 'Ant' to my table:

INSERT INTO `bugtypes` (`BugTypeID`, `BugTypeName`) VALUES
(1, 'Ant');


In order to populate the table with 100 'Ants', I would have to run this command over a hundred times. I know there is a better way to do this, such as a for loop, but I keep screwing up the syntax. Currently this is what I am working with and am continually receiving errors:

CREATE PROCEDURE addAnts(p1 INT)
BEGIN
label1: LOOP
SET p1 = p1 + 1;
IF p1 < 100 THEN
INSERT INTO `bugtypes` (`BugTypeID`, `BugTypeName`) VALUES
(p1, 'Ant');
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
SET @x = p1;
END;


Thanks in advance for the help.

EDIT:

Here is the error I have been receiving:

https://i.gyazo.com/b71ee1c7d6ad41d4a347d97d7cfb6578.png

Answer Source

So I finally found the solution, very similar to @Barmar, except it is simpler. I learned how to add AUTO_INCREMENT to my column BugTypeID thanks to @Barmar as well and that allowed me to do this. Thanks for helping me and pointing me in the right direction guys!

delimiter //

CREATE PROCEDURE doiterate()
BEGIN
  DECLARE total INT unsigned DEFAULT 0;
  WHILE total <= 100 DO
    INSERT INTO `bugtypes` (`BugTypeName`) VALUES ('Ant');
    SET total = total + 1;
  END WHILE;
END//

delimiter ;

CALL doiterate();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download