How To Bulk Insert In MySQL? - Scaler Topics (2024)

The MySQL Bulk Insert refers to a mechanism or command that allows you to efficiently insert a large volume of data into a database table. It is specifically designed to handle high-speed data loading, making it much faster than traditional row-by-row insertion methods.

SQL databases offer specialized statements or functions designed to optimize the MySQL bulk insert processes. However, the specific syntax and implementation can vary based on the database management system (DBMS) being utilized.

There are three ways for performing the MySQL bulk insert which are the following:

Performing MySQL bulk insert using the INSERT Statement variations
Performing MySQL bulk insert using the LOAD DATA INFILE
Performing MySQL bulk insert using an Import Utility

In the next sections, we will go through a detailed explanation of the above methods and how to perform MySQL bulk insert using them.

INSERT Statement Variation for Bulk Inserts

The INSERT statement can be used for performing MySQL bulk insert process. The INSERT statement provides multiple variations that can be used for carrying out the MySQL bulk insert tasks. Following are some INSERT methods that can be used for MySQL bulk insert:

INSERT INTO VALUES Statement

The INSERT INTO ... VALUES is one of the most efficient ways for performing the MySQL bulk insert process. This allows you to insert multiple rows in a single query, reducing the overhead of executing multiple individual insert statements.

Example:

In this example we will create a table employees and bulk insert some values using the INSERT INTO ... VALUES.

Code:

Output:

No output is generated directly from the INSERT INTO statement. However, the rows are inserted into the employees table, and you can verify the success of the operation by querying the table.

Explanation of the example:

First, we create a table called employees with columns id, name, age, and department. The id column is defined as the primary key.
The INSERT INTO statement is used to insert data into the employees table.
We use the VALUES keyword to specify the values to be inserted.
Multiple rows can be inserted at once by providing comma-separated sets of values within parentheses.
Each set of values corresponds to a single row in the table.

In this example, we insert four rows into the employees table with the following values:

Row 1: id=1, name='Peter Parker', age=19, department='IT'

Row 2: id=2, name='Bruce Wayne', age=29, department='Automobile'

Row 3: id=3, name='Tony Stark', age=35, department='Finance'

Row 4: id=4, name='Clark Kent', age=50, department='Media'

INSERT INTO SELECT Statement

The INSERT INTO SELECT statement in MySQL allows you to insert data from one or more tables into another table. It is commonly used for the MySQL bulk insert process when you want to copy data from an existing table or tables into a destination table.

Example:

Suppose we have a table name source_table and we want to copy its data to a new table destination_table.

Code:

Output:

No output is generated directly from the INSERT INTO ... SELECT statement. However, the rows that satisfy the condition (age > 30) are inserted into the destination_table.

Explanation of the example:

First, we create a table called destination_table with columns id, name, age, and salary. The id column is defined as the primary key and the salary column is defined as a decimal number with 10 digits and 2 decimal places.
The INSERT INTO ... SELECT statement is used to insert data into the destination_table based on a query result.
In this example, we select data from an existing table called source_table and insert it into the destination_table.
The SELECT statement retrieves the id, name, age, and salary columns from the source_table.
The WHERE clause is used to filter the rows from the source_table. In this case, we only select rows where the age column is greater than 30.
The selected data is then mapped to the corresponding columns in the destination_table.

INSERT INTO ... ON DUPLICATE KEY UPDATE Statement

The INSERT INTO ... ON DUPLICATE KEY UPDATE statement in MySQL allows you to perform bulk inserts while handling duplicate key conflicts. If a row being inserted violates a unique key or primary key constraint, instead of throwing an error, the ON DUPLICATE KEY UPDATE clause allows you to update the existing row with new values.

Code:

Output:

No output is generated directly from the INSERT INTO ... ON DUPLICATE KEY UPDATE statement. However, the rows are inserted into the employees table, and you can verify the success of the operation by querying the table.

Explanation of the example:

First, we create a table called employees with columns id, name, age, and department. The id column is defined as the primary key.
The INSERT INTO statement is used to insert data into the employees table.
Multiple rows are inserted using the VALUES clause with comma-separated sets of values.
The ON DUPLICATE KEY UPDATE clause is used to handle duplicate key conflicts. If a row with a duplicate primary key (id in this case) is encountered during the insert, the specified columns will be updated instead of generating an error.
In this example, we update the name, age, and department columns using the VALUES() function, which references the values being inserted.

LOAD DATA INFILE

If you're not enthusiastic about writing scripting code, an alternative is to utilize a method such as LOAD DATA INFILE, which is a command specific to MySQL. However, numerous other database management systems (DBMS) offer comparable functionalities. This command enables the importation of various delimited file formats, such as tabs (TDV), commas (CSV), and various others.

Example

In this section, we will learn how to use LOAD DATA INFILE to perform MySQL bulk insert. Suppose you have a CSV file named athletes.csv with the following content:

We will use the LOAD DATA INFILE to perform MySQL bulk insert to insert this data into our athletes table.

Code:

Output:

No output is generated directly from the LOAD DATA INFILE statement. However, the rows from the athletes.csv file are inserted into the athletes table, and you can verify the success of the operation by querying the table.

Explanation of the example:

First, we create a table called athletes with columns id, name, age, and nationality. The id column is defined as the primary key.
The LOAD DATA INFILE statement is used to load data from a file (athletes.csv) into the athletes table.
The INTO TABLE clause specifies the target table where the data will be loaded.
The FIELDS TERMINATED BY ',' clause indicates that the fields in the file are separated by commas.
The LINES TERMINATED BY '\n' clause specifies that each line in the file ends with a new line character.
The IGNORE 1 LINES clause skips the first line of the file, assuming it contains column headers.

Using an Import Utility

The MySQL import utility is used to import data into a MySQL database from a file. It allows for efficient MySQL bulk inserts.

Example

Suppose you have a CSV file named artists.csv with the following content:

We will use the Import Utility to perform MySQL bulk insert to insert this data into our database.

Code:

note; Please substitute the placeholders (your_host, your_username, your_password, and your_database) with the specific details of your MySQL server. Furthermore, ensure to replace /path/to/artists.csv with the precise file path to the artists.csv file stored on your system.

Output:

The MySQL import utility will provide an output indicating the success or failure of the import operation. If there are any errors, they will be displayed in the output.

Explanation of the example:

The --host, --user, and --password options specify the connection details to the MySQL server. Replace them with your own MySQL server credentials.
The --local option indicates that the file to be imported is local to the machine running the MySQL import command.
The --ignore-lines=1 option skips the first line of the file, assuming it contains column headers.
The --fields-terminated-by=',' option specifies that commas separate the fields in the file.
The --lines-terminated-by='\n' option specifies that each line in the file ends with a newline character.
The --columns='id, name, nationality, style' option specifies the column names in the target table, corresponding to the columns in the file.
Finally, you provide the name of your target database and the path to the CSV file to complete the MySQL import command.

Conclusion

MySQL Bulk Insert allows efficient insertion of large volumes of data into a database table, providing faster data loading than traditional row-by-row insertion methods.
The typical workflow of MySQL bulk insert involves preparing the data in a properly formatted file, defining the target table structure, executing the bulk insert command, and monitoring the process for any errors or warnings.
There are three methods for performing MySQL bulk insert: using INSERT statement variations, the LOAD DATA INFILE command, and an Import Utility.
INSERT INTO ... VALUES statement is an efficient way to insert multiple rows in a single query, reducing the overhead of executing individual insert statements.
INSERT INTO SELECT statement allows inserting data from one or more tables into another table, commonly used for bulk inserts when copying data from existing tables.
INSERT INTO ... ON DUPLICATE KEY UPDATE statement handles duplicate key conflicts by updating existing rows with new values instead of throwing an error.
LOAD DATA INFILE command allows importing delimited file formats such as CSV into a MySQL table, providing an alternative to writing scripting code.
The Import Utility is a tool used to import data into a MySQL database from a file, allowing for efficient bulk inserts. It requires specifying connection details, file format, and target table columns.