How to Insert Multiple Rows in MySQL

Last updated on August 5th, 2024 at 06:29 am

MySQL database allows you to easily insert, modify and delete rows of data from database tables. Sometimes you may need to insert multiple rows of data in MySQL. Generally, database developers run separate INSERT statements for each new row to be added. But MySQL allows you to enter single as well as multiple rows of information with a single query. This is more efficient since it reduces the number of queries processed by your database. In this article, we will look at how to insert multiple rows in MySQL.

How to Insert Multiple Rows in MySQL

Here are the steps to insert multiple rows in MySQL. There are multiple ways to insert multiple rows in MySQL. We will look at each of these approaches one by one. Let us say you have the following MySQL table employees(id, first_name, last_name).

mysql> create table employees(id int,
       first_name varchar(255),
       last_name varchar(255));

1. Insert multiple rows using INSERT

Generally, INSERT statement is used to insert a single row of data. In case you need to enter multiple rows, people run different INSERT statements, one for each row. But it is possible to insert multiple database rows using a single INSERT statement. Here is the syntax to insert multiple rows using INSERT statement.

INSERT INTO table_name(column1, column2, ...),
    values(row1_value1, row1_value2,...),
          (row2_value1, row2_value2,...),
          ...

In the above query, you need to mention your table name into which you need to insert values. Next, you need to enter values of each row enclosed in round brackets ‘()’ in a comma-separated manner.

Also read : How to Copy Data from One Table to Another

Here is the SQL query to insert multiple rows of information in employees table.

mysql> insert into employees(id, first_name, last_name)
       values(1,'John','Doe'),
       (2,'Jane','Doe');

mysql> select * from employees;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

This is known as batch insert or bulk insert in MySQL. It allows you to run lesser number of queries and provides better performance.

Also read : How to Update a Column based on Another Column

2. Insert Multiple Rows from SELECT

It can be tedious to construct INSERT statement for multiple rows, in INSERT INTO…VALUES statement. Or sometimes, you may want to copy data from one table to another. In such cases, you can also insert multiple rows of data into your table using the result of a SELECT query.

Here is the SQL query syntax to copy data from one table to another using INSERT INTO statement.

INSERT INTO table1 (column1, column2, ...)
select column1, column2, ...
from table2

In the above query, we select column1, column2, … from table2 and insert them into table1. In this case, MySQL will retrieve the result of SELECT query and use it as an input for INSERT statement.

Please note, the columns used in the INSERT INTO statement and SELECT statement must have same name and order. Otherwise, you will get an error.

Here is the SQL query to copy data from employees table to employees2 table.

mysql> insert into employees2(id, first_name, last_name)
       select id, first_name, last_name
       from employees;

mysql> select * from employees2;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

Also read : How to Update Multiple Columns in MySQL

You can also use this method to copy rows into the same table. This is a great way to easily populate a table with dummy data for testing purposes. Here is the SQL query to copy paste rows from employees table back into it.

mysql> insert into employees(id, first_name, last_name)
       select id, first_name, last_name
       from employees;

mysql> select * from employees2;
+------+------------+-----------+
| id   | first_name | last_name |
+------+------------+-----------+
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
|    1 | John       | Doe       |
|    2 | Jane       | Doe       |
+------+------------+-----------+

3. Insert Multiple Rows Without Duplicate

In the above example, we see that even duplicate rows are inserted into MySQL table. If you want to automatically avoid duplicate records when you insert multiple values in your table, use IGNORE keyword after INSERT in your SQL query.

However, this works only for tables that have a primary key.

Here is an example,

mysql> create table employees(id int primary key,
       first_name varchar(255),
       last_name varchar(255));

mysql> insert ignore into employees(id, first_name, last_name)
            values(1,'John','Doe'),
            (1,'John','Doe');

mysql> select * from employees;
+----+------------+-----------+
| id | first_name | last_name |
+----+------------+-----------+
|  1 | John       | Doe       |
+----+------------+-----------+

As you can see, only 1 row was inserted instead of two duplicate rows.

4. Using LOAD DATA INFILE

Sometimes, you may need to insert data from a text or csv file to your table. You can do this using LOAD DATA INFILE query. Here is the SQL query to load a file’s data into table.

LOAD DATA INFILE '/path/to/file.txt'
INTO TABLE employees
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(id, first_name, last_name);

In the above file, LOAD DATA INFILE is followed by path to text or csv file. We mention the file delimiter using FIELDS TERMINATED BY clause. We also mention line feed character using LINES TERMINATED BY clause.

If you are using CSV file, use ‘,’ for FIELDS TERMINATED BY. If it is a tab delimited text file, then use ‘ ‘ instead. The file extension does not really matter but you need to mention the full file path along with its extension in the above SQL query.

Conclusion

In this article, we have learnt 4 different ways to insert multiple rows of data in MySQL, at once. We have learnt how to do it by mentioning the row values in INSERT query itself. We have also learnt how to insert the result of SELECT query into a table. Lastly, we learnt how to load contents of file into table, using INSERT query. You can use any of these methods, depending on your requirement. But please note, by default, the MySQL server can handle up to 4Mb of data. You can change this using MySQL server settings.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.