INSERT INTO to Add Multiple Rows

1. Getting Started

There are many instances when we may be loading multiple sets of data. It could be inefficient to constantly list out the column list each time, especially if it is the same with each INSERT INTO statement. Luckily, most databases will allow you to include multiple value lists together in a single INSERT statement. The syntax will look like the following:

INSERT INTO <tablename> ( <column1>, <column2>, …)
VALUES (<value1>,<value2>, …),
(<value1>,<value2>, …),
(<value1>,<value2>, …),
(<value1>,<value2>, …);

It’s quite similar to a regular INSERT statement, but instead of having just one set of values, we have each value list separated by commas but enclosed by parentheses.

Let's create a new table to test this on:

CREATE TABLE referral( referral_id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50) );

This statement will create a new table called referral to store the first name, last name, and email of individuals. A standard INSERT statement with one record would look like the following:

INSERT INTO referral (first_name,last_name,email)
VALUES ('Sandra','Boynton','s.boy@email.com');

If we wanted to insert multiple rows at once, the INSERT statement would look like the following:

INSERT INTO referral (first_name,last_name,email)
VALUES ('Randall','Faustino','s.boy@email.com'),
('Park','Deanna','p.deanna@email.com'),
('Sunil','Carrie','s.carrie@email.com'),
('Jon','Brianna','j.brianna@email.com'),
('Lana','Jakoba','l.jakoba@email.com'),
('Tiffany','Walker','t.walk@email.com');

Note, though, if we forgot to include commas between each value list like this:

INSERT INTO referral (first_name,last_name,email)
VALUES ('Randall','Faustino','s.boy@email.com')
('Park','Deanna','p.deanna@email.com')
('Sunil','Carrie','s.carrie@email.com')
('Jon','Brianna','j.brianna@email.com')
('Lana','Jakoba','l.jakoba@email.com')
('Tiffany','Walker','t.walk@email.com');

We would get this error:

Once inserted correctly, we should be able to take a look at what is in the table:

SELECT * 
FROM referral;

Notice that we have the referral_id incrementing automatically. If you were observant, you would also see an error in Randall Faustino’s email. We will cover how to update this in an upcoming tutorial.

2. Using the RETURNING Statement

Let's recreate the table so that we can look at another example, although this is specific to PostgreSQL. Run the following statement to drop the table:

DROP TABLE referral;

CREATE TABLE referral( referral_id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(50) );

It can be useful to add RETURNING * at the end of the INSERT INTO statement. By doing so, we’ll automatically query the results and output them to the user.

INSERT INTO referral (first_name,last_name,email)
VALUES ('Randall','Faustino','s.boy@email.com'),
('Park','Deanna','p.deanna@email.com'),
('Sunil','Carrie','s.carrie@email.com'),
('Jon','Brianna','j.brianna@email.com'),
('Lana','Jakoba','l.jakoba@email.com'),
('Tiffany','Walker','t.walk@email.com')
RETURNING *;

If we accidentally ran the exact statement again, it should run, as we did not have any constraints preventing our data from being inserted:

Notice that the referral_id moves up to 7 now. If we queried the entire table, we should see the records repeated:

Instead of having RETURNING *, we can also specify the columns to return. For example, we could use the referral_id as it is being auto-generated:

INSERT INTO referral (first_name,last_name,email)
VALUES ('Randall','Faustino','s.boy@email.com'),
('Park','Deanna','p.deanna@email.com'),
('Sunil','Carrie','s.carrie@email.com'),
('Jon','Brianna','j.brianna@email.com'),
('Lana','Jakoba','l.jakoba@email.com'),
('Tiffany','Walker','t.walk@email.com')
RETURNING referral_id;

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.