How to Insert Multiple Rows in MySQL at a Time

In this tutorial, we are going to see how to insert multiple rows in MySQL at a time. To insert multiple rows into a table, you use the following syntax of the INSERT statement:

INSERT INTO tableName (column1, column2, ..., columnN)
VALUES
     (value1, value2, ..., valueN),
     (value1, value2, ..., valueN),
     ...
     (value1, value2, ..., valN);

 
Let’s take a simple example to see how it works. For this we will use the “Customers” table.

CREATE TABLE Customers (
    CustomerID int AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(20) NOT NULL,
    Age int,
    Address VARCHAR(100)
);

Normally we can insert a ‘Customer’ like this:

INSERT INTO Customers (CustomerID, Name, Age, Address) 
VALUES (1, 'Alex', 20, 'San Francisco');

To insert multiple rows at once, we can do so by separating each set of values with a comma:

INSERT INTO Customers 
  (CustomerID, Name, Age, Address)
VALUES
  (1, 'Alex', 20, 'San Francisco'),
  (2, 'Emily', 22, 'Los Angeles'),
  (3, 'Thomas', 15, 'New York'),
  (4, 'Bob', 33, 'Austin'),
  (5, 'Yohan', 16, 'Boston');

You can also neglect the name of the fields like this:

INSERT INTO Customers
VALUES
  (1, 'Alex', 20, 'San Francisco'),
  (2, 'Emily', 22, 'Los Angeles'),
  (3, 'Thomas', 15, 'New York'),
  (4, 'Bob', 33, 'Austin'),
  (5, 'Yohan', 16, 'Boston');

But it is recommended to include the name of the fields in case your table schema changes. If this happened, your query would be interrupted because the number and/or type of fields in your query would be different from the structure of the table.
 

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More