How to Insert Multiple Rows in MySQL

In MySQL, you can insert multiple rows into a table using a single SQL statement. This is known as bulk insert or multi-row insert. In this tutorial, we'll show you how to insert multiple rows into a MySQL table using the INSERT statement.

Syntax

The syntax for inserting multiple rows into a MySQL table is as follows:

INSERT INTO table_name(column1, column2, column3, ...)
VALUES (value1, value2, value3, ...),
       (value1, value2, value3, ...),
       (value1, value2, value3, ...),
       ...

Each set of values is separated by a comma and enclosed in parentheses. You can insert as many rows as you want in a single INSERT statement.

Example

Let's say we have a table called "employees" with three columns - "id", "name", and "salary". Here's how we can insert multiple rows into the table using a single INSERT statement:

INSERT INTO employees(id, name, salary)
VALUES (1, 'John', 50000),
       (2, 'Mary', 60000),
       (3, 'Bob', 70000),
       (4, 'Jane', 80000),
       (5, 'Tom', 90000);

This will insert five rows into the "employees" table.

Output

When we execute the above SQL statement, we won't get any output. But if we select all rows from the "employees" table, we can see the rows that we just inserted:

SELECT * FROM employees;

+----+------+--------+
| id | name | salary |
++------+--------+
|  1 | John |  50000 |
|  2 | Mary |  60000 |
|  3 | Bob  |  70000 |
|  4 | Jane |  80000 |
|  5 | Tom  |  90000 |
+----+------+--------+

Explanation

In the example above, we used a single INSERT statement to insert multiple rows into the "employees" table. We specified the columns we wanted to insert data into (id, name, and salary) and then provided the values for each row in parentheses, separated by commas. We inserted five rows into the table.

Use

Inserting multiple rows into a MySQL table using a single INSERT statement can save time and improve performance, especially when dealing with large amounts of data. It is also more concise and easier to read than writing separate INSERT statements for each row.

Important Points

• Make sure the number of values you provide for each row matches the number of columns you specified in the INSERT statement.
• The values need to be in the same order as the columns specified in the INSERT statement.
• You can also insert data into some columns and let the database fill in default values for others.

Summary

In this tutorial, we showed you how to insert multiple rows into a MySQL table using a single INSERT statement. We covered the syntax, example, output, explanation, use, and important points of bulk insert in MySQL. With this knowledge, you can efficiently insert multiple rows into your MySQL database.