MySQL: Insert in a loop

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
• MySQL Insert Date Loop

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:-

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 !!!