How to execute a insert query multiple times into a table with mySql

Question

For testing the TTFB for single pages loaded by a CMS I wan't to add new pages into my database without doing it one for one inside a CMS. For example I wan't to execute the query as below a 100 times inside a table, but how can I do this in mySql/phpmyadmin?

This query must be execute for 100 times

INSERT INTO `bolt_pages` (`id`, `slug`, `datecreated`, `datechanged`, `datepublish`, `datedepublish`, `username`, `ownerid`, `status`, `templatefields`, `title`, `image`, `teaser`, `body`, `template`) VALUES (NULL, 'hello-world', '2017-05-15 12:01:35', '2017-05-15 13:22:43', '2017-05-15 12:01:13', NULL, '', '1', 'published', '[]', 'Hello world', NULL, '<p>teaser</p>\r\n', '<p>Hello world</p>\r\n', '');

Create a stored procedure. You can even have a parameter for the number of inserts to execute, — jarlh, May 15 '17 at 12:08
http://stackoverflow.com/questions/6926264/how-can-i-run-a-query-multiple-times-in-phpmyadmin — Shefali, May 15 '17 at 12:18

score 6 · Accepted Answer · answered May 15 '17 at 12:24

With a procedure should be...

DELIMITER $$
CREATE PROCEDURE simple_loop ( )
BEGIN
  DECLARE counter BIGINT DEFAULT 0;

  my_loop: LOOP
    SET counter=counter+1;

    IF counter=100 THEN
      LEAVE my_loop;
    END IF;

    #SELECT counter; #uncomment if you'd like to print the counter

    INSERT INTO `bolt_pages` (`id`, `slug`, `datecreated`, `datechanged`, `datepublish`, `datedepublish`, `username`, `ownerid`, `status`, `templatefields`, `title`, `image`, `teaser`, `body`, `template`) VALUES (NULL, 'hello-world', '2017-05-15 12:01:35', '2017-05-15 13:22:43', '2017-05-15 12:01:13', NULL, '', '1', 'published', '[]', 'Hello world', NULL, '<p>teaser</p>\r\n', '<p>Hello world</p>\r\n', '');

  END LOOP my_loop;
END$$
DELIMITER

Ilyes · Answer 2 · 2017-05-15T12:32:36.320

1

You can use Repeat , or you can create a procedure as well:

SET @K = 0;
    lab1: REPEAT
    INSERT INTO `bolt_pages` (`id`, `slug`, `datecreated`, `datechanged`, `datepublish`, `datedepublish`, `username`, `ownerid`, `status`, `templatefields`, `title`, `image`, `teaser`, `body`, `template`) VALUES (NULL, 'hello-world', '2017-05-15 12:01:35', '2017-05-15 13:22:43', '2017-05-15 12:01:13', NULL, '', '1', 'published', '[]', 'Hello world', NULL, '<p>teaser</p>\r\n', '<p>Hello world</p>\r\n', '');
    Set @k = @k +1;
    UNTIL @K > 100 END REPEAT lab1;

edited May 15 '17 at 12:32

answered May 15 '17 at 12:18

Ilyes

14,640
4
29
55

When I wan't to execute this query, it say's that `Repeat` is a not recognized command. – CodeWhisperer May 15 '17 at 12:23
1

Getting SQL Error (1064) .. syntax to use near 'REPEAT Set @k = @k +1' at line 1. Mysql Server version is 5.7.19-log. – Sandeepan Nath Mar 01 '19 at 09:59

How to execute a insert query multiple times into a table with mySql

2 Answers2