How to Insert Multiple Rows in MySQL

Sometimes you may need to insert multiple rows of data in MySQL. MySQL allows you to enter multiple rows of information with a single query. 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.

Insert multiple rows using INSERT

Let us say you have the following table employees(id, first_name, last_name).

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

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       |
+------+------------+-----------+

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

Insert Multiple Rows from SELECT

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.

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

Insert Multiple Rows Without Duplicate

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.

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