Running an SQL query multiple times

Question

I need to run this query 100 times to input data into my MySQL database. ID auto increments. Not concerned about incrementing the table Name column. Just need to fill the database. What is the best way to go about this without having to copy/paste 100 times?

"INSERT INTO `tables`(id, name, assigned_seating, open_seating, position) VALUES ('', 'Table 1', 0, 1, '')";

can be done in one query, dont need 100, give me a mo to write that — , Feb 07 '17 at 20:53
You can do it in eight queries total if you follow that with 7 `INSERT....SELECT`s ... though technically you end up with 128 rows then. — Uueerdo, Feb 07 '17 at 21:05
Possible duplicate of [Inserting multiple rows in mysql](http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql) — Cave Johnson, Feb 08 '17 at 01:17

Paul Spiegel · Answer 1 · 2017-02-08T01:02:47.697

1

All you need is an existing table with at least 100 rows. I will use information_schema.columns as example:

INSERT INTO `tables`(id, name, assigned_seating, open_seating, position)
    SELECT null, 'Table 1', 0, 1, ''
    FROM information_schema.columns
    LIMIT 100;

Demo: http://rextester.com/DMSC23853

edited Feb 08 '17 at 01:02

answered Feb 08 '17 at 00:35

Paul Spiegel

30,925
5
44
53

score 1 · Accepted Answer · answered Feb 08 '17 at 01:28

If anyone sees this in the future, this is the best answer

public function addbatch()
{
  for ($i = 1; $i <= 100; $i++)
  {
    $tableName = "Table " . $i;
    $q = "INSERT INTO `tables`(id, name, cap, assigned_seating, open_seating, position) VALUES ('', '".$tableName."', 10, 0, 1, '')";
    $this->db->query($q);
  }
}

call function once. Make sure to delete when done though!

score 0 · Answer 3 · answered Feb 07 '17 at 20:55

0

You can do a Batch insert:

insert into Table
(column1, column2)
    VALUES
      ('value 1', 'value2') ,
      ('value3', 'value4')

You can do as many rows as you want as long as you separate them by comas.

answered Feb 07 '17 at 20:55

Gerardo Jaramillo

455
4
10

score 0 · Answer 4 · answered Feb 07 '17 at 21:05

 $vals='';
 for ($i = 0; $i < 100; $i++) {
    $vals.="('Table 1', 0, 1, ''),";
 }
 $vals=rtrim($vals,',');

 mysqli_query($dbh, 'INSERT INTO `tables`(name, assigned_seating, open_seating, position) VALUES ' . $vals);

assumed id was auto incremented so just leave it out of the query

score 0 · Answer 5 · answered Feb 07 '17 at 23:55

try this:

DELIMITER $$

DROP PROCEDURE IF EXISTS `multipleInsert` $$
    CREATE PROCEDURE `multipleInsert`(in n int)
    BEGIN
    DECLARE cont int default 0;
    WHILE cont  < n DO
    INSERT INTO `tables`(id, name, assigned_seating, open_seating, position) VALUES ('', 'Table 1', 0, 1, '');
    set cont  = cont  + 1;
    end while;
    END $$

DELIMITER ;

Call Procedure:

call multipleInsert(100);

Running an SQL query multiple times

5 Answers5