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 — user6763587
– user6763587, Commented Feb 7, 2017 at 20:53
You can do it in eight queries total if you follow that with 7 INSERT....SELECTs ... though technically you end up with 128 rows then. — Uueerdo
– Uueerdo, Commented Feb 7, 2017 at 21:05

Paul Spiegel · Accepted Answer · 2017-02-08 01:02:47Z

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 8, 2017 at 1:02

answered Feb 8, 2017 at 0:35

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

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

Comments

user7531085 · Accepted Answer · 2017-02-08 01:28:18Z

1

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!

answered Feb 8, 2017 at 1:28

user7531085

Comments

Gerardo Jaramillo · Accepted Answer · 2017-02-07 20:55:31Z

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 7, 2017 at 20:55

Gerardo Jaramillo

4854 silver badges11 bronze badges

Comments

user6763587 · Accepted Answer · 2017-02-07 21:05:14Z

0

 $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

answered Feb 7, 2017 at 21:05

user6763587

Comments

Danilo Bustos · Accepted Answer · 2017-02-07 23:55:57Z

0

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);

answered Feb 7, 2017 at 23:55

Danilo Bustos

1,0931 gold badge7 silver badges9 bronze badges

Collectives™ on Stack Overflow

Running an SQL query multiple times

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related