Online College Courses for Credit

+
INSERT INTO to Add Multiple Rows

INSERT INTO to Add Multiple Rows

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose an INSERT statement that adds 3 records to an existing table in one statement.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using the INSERT INTO command to add multiple rows into a table in two parts:

  1. Getting Started
  2. RETURNING statement

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) );

File:11353-1420-1.png

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:

File:11354-1420-2.png

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

SELECT *
FROM referral;

File:11355-1420-3.png

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 *;

File:11356-1420-4.png

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

File:11357-1420-5.png

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

File:11358-1420-6.png

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;

File:11359-1420-7.png


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.

summary