This article will discuss how to insert rows in a MySQL table using a loop. Apart from integer and string data types, we will also cover how to insert dates in a loop and control loops with date-specific conditions.
Table of Contents
MySQL Insert Rows in a Loop
This section will create a table student_data and insert some rows into it using a do-while loop.
CREATE TABLE student_data ( id INT AUTO_INCREMENT, enroll_id INT, term VARCHAR(50), specialization VARCHAR(50), PRIMARY KEY (id) );
Let us see if the table student_data got created by executing:
SELECT * FROM student_data;
Output:-

The table is empty at the moment. We will now be inserting rows into it using a do-while loop. The below query creates a procedure named insertRowsTostudent_data() to do the task.
DROP PROCEDURE IF EXISTS insertRowsTostudent_data; DELIMITER // CREATE PROCEDURE insertRowsTostudent_data() BEGIN DECLARE i INT DEFAULT 1; WHILE (i <= 15) DO INSERT INTO student_data ( enroll_id,term,specialization) VALUES (i,"term1", "Computers"); SET i = i+1; END WHILE; END; // DELIMITER ;
Explanation:-
Frequently Asked:
- Find all tables with specific column names in MySQL
- MySQL select first row in each group
- MYSQL: change column order
- Mysql update column with value from another table
Here in the above procedure insertRowsTostudent_data(), we are inserting rows in a loop:
- The value for column id is inserted automatically via AUTO INCREMENT.
- Column enroll_id is inserting the values using the variable i.
- Values for column term and specialization are inserted with constant values ‘term1‘ and ‘Computers‘, respectively.
- We are inserting 15 rows into the table, starting the enroll_id from 1 and incrementing it with 1 till its value becomes 15.
- Compare the value of the variable i if it is less than or equal to 15. Then insert the row else end the loop.
Call the procedure insertRowsTostudent_data().
CALL insertRowsTostudent_data();
Let us see what got added to the table student_data by executing:
SELECT * FROM student_data;
Output:-

MySQL Insert Date Loop
This section will be inserting dates in a loop, incrementing the value by one day. Let us create another table student_enroll_date with a date field.
CREATE TABLE student_enroll_date ( student_id INT, enroll_date DATE ); SELECT * FROM student_enroll_date;
Output:-

The table student_enroll_date is empty at the moment. We will now be inserting rows into it using a do-while loop. The below query creates a procedure loop_date() to do the task.
DROP PROCEDURE IF EXISTS loop_date; DELIMITER // CREATE PROCEDURE loop_date() BEGIN DECLARE i INT DEFAULT 1; DECLARE date_var date DEFAULT '2021-01-01'; WHILE (date_var < '2021-02-20') DO INSERT INTO student_enroll_date (student_id,enroll_date) VALUES (i,date_var); set date_var = date_var + INTERVAL 1 DAY; SET i = i+1; END WHILE; END; // DELIMITER ;
Explanation:-
Here in the above procedure, we are inserting rows:
- Values of the column student_id are inserted using the variable i.
- Column enroll_date is pulling the values to be inserted from variable date_var.
- We are inserting 50 rows to the table staring the student_id from 1 and enroll_date from ‘2021-01-01’ incremented by an interval of 1 day.
- Compare the value of date_var if it is earlier than date ‘2021-02-20’. Then insert the row else end the loop.
Call the procedure loop_date().
CALL loop_date();
Let us see what got added to the table student_enroll_date by executing:
SELECT * FROM student_enroll_date LIMIT 10;
Output:-

There are 50 rows inserted to the table student_enroll_date, but we are fetching only 10 rows in the output using the LIMIT clause.
Read More About INSERT :
- MYSQL INSERT INTO
- MYSQL INSERT FROM SELECT
- Insert into a MySQL table or update if exists
- INSERT record if NOT EXISTS in MySQL
We hope this article helped you understand how to insert rows in a MySQL table using the loop. Good Luck !!!