Oracle INSERT INTO SELECT

Summary: in this tutorial, you will learn how to use the Oracle INSERT INTO SELECT statement to insert data into a table from the result of SELECT statement.

Introduction to Oracle INSERT INTO SELECT statement #

Sometimes, you want to select data from a table and insert it into another table. To do it, you use the Oracle INSERT INTO SELECT statement as follows:

INSERT INTO target_table (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM source_table
WHERE condition;Code language: SQL (Structured Query Language) (sql)

The Oracle INSERT INTO SELECTstatement requires the data type of the source and target tables to match.

If you want to copy all rows from the source table to the target table, you remove the WHERE clause. Otherwise, you can specify which rows from the source table should be copied to the target table.

Oracle INSERT INTO SELECT examples #

Let’s take some examples of using the INSERT INTO SELECT statement.

Inserting all sales data example #

First, create a table calledsales for the demonstration:

CREATE TABLE sales (
  customer_id NUMBER,
  product_id NUMBER,
  order_date DATE NOT NULL,
  total NUMBER (9, 2) DEFAULT 0 NOT NULL,
  PRIMARY KEY (customer_id, product_id, order_date)
);Code language: SQL (Structured Query Language) (sql)

Try it

Second, insert a sales summary from the orders and order_items tables into the sales table:

INSERT INTO
  sales (customer_id, product_id, order_date, total)
SELECT
  customer_id,
  product_id,
  order_date,
  SUM(quantity * unit_price) amount
FROM
  orders
  INNER JOIN order_items USING (order_id)
WHERE
  status = 'Shipped'
GROUP BY
  customer_id,
  product_id,
  order_date;Code language: SQL (Structured Query Language) (sql)

Try it

Third, retrieve data from the sales table to verify the insert:

SELECT
  *
FROM
  sales
ORDER BY
  order_date DESC,
  total DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Inserting partial sales data example #

Suppose you want to copy only sales summary data in 2017 to a new table. Here are the steps to do so:

First, create a new table called sales_2017 as follows:

CREATE TABLE sales_2017 
AS SELECT
    *
FROM
    sales
WHERE
    1 = 0;
Code language: SQL (Structured Query Language) (sql)

Try it

The condition in the WHERE clause ensures that the data from the sales table is not copied to the sales_2017 table.

Second, use the INSERT INTO SELECT with a WHERE clause to copy 2017 sales data to the sales_2017 table:

INSERT INTO sales_2017
    SELECT customer_id,
           product_id,
           order_date,
           SUM(quantity * unit_price) amount
    FROM orders
    INNER JOIN order_items USING(order_id)
    WHERE status = 'Shipped' AND EXTRACT(year from order_date) = 2017        
    GROUP BY customer_id,
             product_id,
             order_date; 
Code language: SQL (Structured Query Language) (sql)

Try it

In this example, we don’t specify the column list in the INSERT INTO clause because the result of the SELECT statement has the values that correspond to the columns of the sales_2017 table.

Additionally, we add more condition to the WHERE clause of the SELECT statement to retrieve only sales data in 2017.

Third, select all data from the sales_2017 table:

SELECT *
FROM sales_2017
ORDER BY order_date DESC,
         total DESC;Code language: SQL (Structured Query Language) (sql)

Try it

Inserting partial data and literal value example #

Suppose you want to send emails to all customers to announce new products. To do it, you can copy customer data to a separate table and track email sending status.

First, create a new table calledcustomer_lists as follows:

CREATE TABLE customer_lists(
    list_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name varchar2(255) NOT NULL,
    last_name varchar2(255) NOT NULL,
    email varchar2(255) NOT NULL,
    sent NUMBER(1) NOT NULL,
    sent_date DATE,
    PRIMARY KEY(list_id)
);
Code language: SQL (Structured Query Language) (sql)

Try it

Second, copy data from the contacts table to the customer_lists table:

INSERT INTO
  customer_lists (first_name, last_name, email, sent)
SELECT
  first_name,
  last_name,
  email,
  0
FROM
  contacts;Code language: SQL (Structured Query Language) (sql)

Try it

In this example, in addition to retrieving data from the contacts table, we use literal 0 as the value for the sent column.

Third, retrieve the data from the customer_lists table:

SELECT * FROM customer_lists;

Try it

Output:

Note that this example is just for demonstration, you can add DEFAULT 0 to the definition of the sent column.

Summary #

• Use the Oracle INSERT INTO SELECT statement to insert data into a table from the result of a query.