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:
Open a text file and type the following data:
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:
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:
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:
To open and verify the file:
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:
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:
We will insert the result of five students using the single command:
To display the contents of the table:
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.
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.