MySQL Insert Multiple Rows Single Query Syntax

This article provides a clear and concise guide on how to insert multiple rows of data into a MySQL table using a single INSERT statement. You will learn the exact SQL syntax, view a practical example, and understand the performance benefits of using this multi-row insertion method instead of executing multiple individual queries.

The Syntax

To insert multiple rows into a MySQL table with a single query, you extend the standard INSERT INTO statement by separating each row’s data with a comma inside the VALUES clause.

Here is the basic syntax:

INSERT INTO table_name (column_1, column_2, column_3)
VALUES
    (value_1a, value_1b, value_1c),
    (value_2a, value_2b, value_2c),
    (value_3a, value_3b, value_3c);

Key Components of the Syntax:

• table_name: The name of the table where you want to insert the data.
• (column_1, column_2, column_3): A comma-separated list of the columns you want to populate.
• VALUES: The keyword that precedes the list of data rows.
• (...): Each set of parentheses represents a single row of data. Each value inside must match the order of the columns defined above.
• , (Comma): Used to separate each row block.
• ; (Semicolon): Placed at the very end of the statement to terminate the query.

Practical Example

Suppose you have a table named employees with three columns: first_name, last_name, and department.

To insert three new employees at the same time, you would run the following query:

INSERT INTO employees (first_name, last_name, department)
VALUES
    ('John', 'Doe', 'Sales'),
    ('Jane', 'Smith', 'Marketing'),
    ('Michael', 'Brown', 'IT');

Benefits of Using Single Statement Insertion

Using a single statement to insert multiple rows is highly recommended over running multiple individual INSERT queries for several reasons:

1. Faster Performance: It drastically reduces the overhead of sending multiple queries from your application to the database server.
2. Reduced Network Traffic: Sending one large query package uses less bandwidth than sending dozens of individual queries.
3. Faster Index Rebuilding: MySQL buffers the index changes and updates them once after the query completes, rather than rebuilding indexes after every single row insertion.