INSERT INTO to Add Multiple Rows

1. Getting Started

There are many instances where we may be loading multiple sets of data. It could be inefficient to constantly list out the column list each time especially if they are the same with each INSERT INTO statement. Luckily, most databases will allow you to include multiple value lists together into 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>, …);

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

Let us 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 in 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:

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 get into updating this in an upcoming tutorial.

2. RETURNING statement

Let us 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.