How to Insert Multiple Rows in SQL

Problem

You want to insert multiple rows into an SQL table using one query instead of one insert per query.

Example

You have a table called Customers with columns CustomerID, Name, Email, and Address. Let’s look at the table:

You have a list of new customers you want to add to the database table. Instead of inserting each customer individually, use multiple row insertion techniques to expedite the process.

Solution

Let’s look at a query that inserts four new customers:

INSERT INTO Customers (CustomerID, Name, Email, Address) VALUES
   (5, 'John Doe', 'jdoe@email.com', '123 Main St'),
   (6, 'Jane Smith', 'jsmith@email.com', '456 Elm St'),
   (7, 'Alex Johnson', 'ajohnson@email.com', '789 Oak St'),
   (8, 'Mary Brown', 'mbrown@email.com', '987 Boar St');

And this is what you get if you select all data in the table:

SELECT * FROM Customers;

The result:

Discussion

In this syntax, use multiple comma-separated lists of values for insertion instead of a single list of values. After the INSERT keyword, specify in parentheses the column names into which you want to insert. Then, put the VALUES keyword and then list the values for the new rows. Each new row is given in parentheses, and the values are given in the same order as the column names. Separate each row with parentheses.

If you give values for all columns in the database, you may omit the column names. See the example below. Note that the values for the rows must be given in the same order as that of the columns in the table definition.

INSERT INTO Customers VALUES
   (5, 'John Doe', 'jdoe@email.com', '123 Main St'),
   (6, 'Jane Smith', 'jsmith@email.com', '456 Elm St'),
   (7, 'Alex Johnson', 'ajohnson@email.com', '789 Oak St'),
   (8, 'Mary Brown', 'mbrown@email.com', '987 Boar St');

When inserting multiple rows with one INSERT statement, keep in mind the constraint of this method: the maximum number of rows you can insert in one statement is 1,000. If you want to insert more than that, consider using multiple INSERT statements.

While this insertion method is not mandatory, using multiple-row insertion in SQL significantly reduces the time and effort required to insert a large number of rows into a database table.