Online College Courses for Credit

+
INSERT to Add Queried Data

INSERT to Add Queried Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Compose an insert statement that uses a SELECT statement to pull queried data.

(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 SELECT statement with the INSERT INTO statement to add data from other tables in two parts:

  1. INSERT with SELECT
  2. Summary Data

1. INSERT with SELECT

The INSERT feature using a SELECT clause can come in quite handy when we need to load data into a table from another table. It could be to load data into an existing table, a newly created table or into a temporary table that includes some calculations. Anything that can be queried can be subsequently added to an INSERT statement to be inserted into a table.

The structure of the statement would look like:

INSERT INTO <tablename> ( <column1>, <column2>, …)
<SELECT …>;

Similar to the INSERT INTO command from the prior tutorials, we do want to include the columns and the order the query should return their values. In this structure though, we replace the value list with a complete SELECT statement.

Let us say that we have created a contact table and would like to add in the customer first name, last name and phone number. We would like to have only the customers that live in the country USA as the table will be used for customer service representatives to make the calls. The first step is to create the table that we’ll be using. Note that we will want to ensure that the data types are the same and the sizes will be the same or larger to ensure we do not have any issues with truncated data.

CREATE TABLE contact( contact_id SERIAL, first_name VARCHAR(40), last_name VARCHAR(40), phone VARCHAR(24) );

The next step is to create and validate the SELECT statement which will look like this:

SELECT first_name, last_name, phone
FROM customer
WHERE country = 'USA';

File:11360-1430-1.png

We can now wrap it into the INSERT statement like:

INSERT INTO contact (first_name, last_name, phone)
SELECT first_name, last_name, phone
FROM customer
WHERE country = 'USA';

Note that the first_name, last_name, and phone in the first line represent the columns from the contact table rather than from the customer table although they are named the same. Now that this is complete, we can run a query on the contact table to verify that the rows have been inserted:

SELECT *
FROM contact;

File:11361-1430-2.png


2. Summary Data

There are many instances where this can be useful. For example, if we’re loading data from another table/database/file but we need to have the data formatted in a specific way, rather than ALTER an existing table’s properties, we can build the table as we would want it and then copy the data in. We could also use this to build a summary table to get point in time data recorded.

Let us take a look at an example of that. As an example, we may want to have a summary table that has the invoice total, a number of invoices up to the current date with the added date inserted. Since this is a point in time data, it can be a bit harder to capture those changes without a bit more complex set of criteria.

CREATE TABLE invoice_summary( summary_date date, all_total numeric, num_of_invoice integer );

With the table created, we can now build the SELECT statement to generate the point in time data that includes the date:

SELECT now(), SUM(total), COUNT(invoice_id)
FROM invoice;

As a reminder, the now() function returns the current date/time. Putting this together now with the SELECT statement, it would look like this:

INSERT INTO invoice_summary(summary_date,all_total,num_of_invoice)
SELECT now(), SUM(total), COUNT(invoice_id)
FROM invoice;

We can now query the invoice_summary table to see the new record that was added:

File:11362-1430-3.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
We can use a SELECT statement to INSERT data into a table using data from another table.