Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle INSERT INTO SELECT

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.

Overview of 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;

The Oracle INSERT INTO SELECTstatement requires the data type of the source and target tables 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

A) Insert all sales data example

Let’s create a table named sales 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) );

The following statement inserts 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;

The following statement retrieves data from the sales table to verify the insert:

SELECT * FROM sales ORDER BY order_date DESC, total DESC;
Oracle INSERT INTO SELECT statement example

B) Insert partial sales data example

Suppose, you want to copy only sales summary data in 2017 to a new table. To do so, first, you create a new table named sales_2017 as follows:

CREATE TABLE sales_2017 AS SELECT * FROM sales WHERE 1 = 0;

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

In this example, we didn’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. In addition, we added a more condition in the WHERE clause of the SELECT statement to retrieve only sales data in 2017.

The following query selects all data from the sales_2017 table:

SELECT * FROM sales_2017 ORDER BY order_date DESC, total DESC;
Oracle INSERT INTO SELECT copy data partially

C) Insert 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 named customer_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) );

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;

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

The following query retrieves the data from the customer_lists table:

Oracle INSERT INTO SELECT with literals example

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

In this tutorial, you have learned how to use the Oracle INSERT INTO SELECT statement to insert data into a table from the result of a query.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle INSERT
Next Oracle UPDATE

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.