How to bulk insert data in MySQL

MySQL is a popular RDMS used to manage the data of a website or application by using the query language known as SQL. The data of websites are stored in the form of tables, and for inserting large amounts of data would be quite time-consuming, for this purpose SQL offers different ways to insert data in bulk. In this post, we will learn the insertion of the bulk of data using a single query in the table of MySQL.

How to insert bulk data in MySQL

Many times a lot of entries are to be made in the database using the same query, for example, to make a result card of students, instead of inserting every student’s result record separately which will take a lot of time, it is recommended to update the record of all the students using the single query.

There are two different ways for bulk inserting data in MySQL.

Method 1: Using LOAD DATA statement with CSV file

Another method is inserting data from a CSV file, to understand this, we will create a table of Teachers names with their subjects which they are teaching in a class using a command:

CREATE TABLE teacher_names (teacher_id INT, teacher_name VARCHAR(50), subject VARCHAR(50));

Open a text file and type the following data:

teacher_id,teacher_name,subject

1,”John”,”English”

2,”Sophia”,”Science”

3,”Paul”,”Arts”

Save the text file by name “teacher_names.csv”. You may encounter an error of –secure-file-priv option while loading the data, as shown in the image below:

LOAD DATA INFILE '/home/teacher_names.csv' INTO TABLE teacher_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

To resolve this issue you need to move the teacher_names.csv to the secure_file_priv variable folder. Execute the command to locate the path of variable secure_file_priv:

SHOW VARIABLES LIKE "secure_file_priv";

Now move the csv file to the /var/lib/mysql-myfiles folder:

Run the following command to import all the data from the teacher_names.csv file to the teacher_names table of MySQL:

LOAD DATA INFILE '/var/lib/mysql-files/teacher_names.csv' INTO TABLE teacher_names FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

To open and verify the file:

SELECT * FROM teacher_names;

Method 2: Using INSERT INTO statement

The first method is using the insert command for the insertion of bulk data. Let us discuss the general syntax of using the command to insert bulk data in MySQL.

Syntax to insert bulk data in MySQL

The general syntax of inserting bulk values in a table in MySQL is:

INSERT INTO table_name VALUES (data), (data), (data);

The explanation to the above general syntax is simple:

Type the clause INSERT INTO and the table name in which you want to insert the data
Use the clause VALUES and then in the brackets write the data of the first row, close the brackets, and after the put the comma
After the comma use the brackets and enter the data of the other row and so on

To understand how this works, let us consider an example, we will create a table of “class_result”, using the command:

CREATE TABLE class_result (st_id INT, st_name VARCHAR(50), st_grade CHAR(25));

We will insert the result of five students using the single command:

INSERT INTO class_result VALUES (1,’John’,’A’),(2,’Elsa’,’D’),(3,’Sophia’,’B’),(4,’Paul’,’B’),(5,’Saira’,’A’);

To display the contents of the table:

SELECT*FROM class_result;

From the above output, we see that we have inserted a bulk of data using a single query instead of inserting the data by different queries.

Conclusion

It saves a lot of time for inserting a bulk of data using a single query in MySQL. In this post, we learn the way of inserting the bulk of values in the table of MySQL using a single command. We created a table, inserted multiple rows of records in the tables by using a single MySQL query, and tried to explain how bulk data can be inserted into the table of MySQL. We also explain inserting the data from the CSV format file in the MySQL table using the query of the LOAD TABLE.