How to Insert Bulk Values in MySQL

1. Insert Singular Entry Using the INSERT Statement
2. Insert Bulk Values Using the INSERT Statement

This tutorial aims to understand how to insert bulk values in MySQL using the INSERT statement.

Before starting with bulk values, let us understand how singular rows or entries are filled using the INSERT statement.

The INSERT INTO statement is used to insert new records in a table. To execute this, we need to add two things to our statement:

1. Table name and column name where the data is to be inserted.
2. Values to be inserted.

Let us try to understand how this statement works.

Before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);

Insert Singular Entry Using the INSERT Statement

The above query lets us create a table with the name student_details. Now with the help of the INSERT statement, let us try to add data for a few students. This operation can be done as follows:

-- insert rows to the table student_details
INSERT INTO student_details
 VALUES(1,"Preet","Sanghavi");

The code above would enter the student data in the table student_details. We can visualize this table with the following command:

SELECT * from student_details;

The above stated code block would generate the following output:

stu_id	stu_firstName	stu_lastName
1		Preet			Sanghavi

Insert Bulk Values Using the INSERT Statement

While the above approach helps us add data, it is impossible to add data for multiple users. To make this task easier, we make use of the following syntax to add multiple values in the table:

INSERT INTO table_name (col_1, col_2, col_3)
VALUES (value_1_row_1, value_2_row_1, value_3_row_1), 
(value_1_row_2, value_2_row_2, value_3_row_2);

Let us try to insert data for multiple students at the same time using the syntax above. We can do this by using the following:

-- insert bulk rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The code above lets us insert bulk entries and would generate the following:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Thus, with the help of the INSERT statement, we can efficiently enter singular and bulk rows at a time. Generally, bulk entries are made in the production environment to save time and resources.