Oracle / PLSQL: INSERT Statement

This Oracle tutorial explains how to use the Oracle INSERT statement with syntax and examples. We've also added some practice exercises that you can try for yourself.

Description

The Oracle INSERT statement is used to insert a single record or multiple records into a table in Oracle.

Syntax

The syntax for the Oracle INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table
(column1, column2, ... column_n )
VALUES
(expression1, expression2, ... expression_n );

Or the syntax for the Oracle INSERT statement when inserting multiple records using a SELECT statement is:

INSERT INTO table
(column1, column2, ... column_n )
SELECT expression1, expression2, ... expression_n
FROM source_table
[WHERE conditions];

Parameters or Arguments

table

The table to insert the records into.

column1, column2, ... column_n

The columns in the table to insert values.

expression1, expression2, ... expression_n

The values to assign to the columns in the table. So column1 would be assigned the value of expression1, column2 would be assigned the value of expression2, and so on.

source_table

The source table when inserting data from another table.

WHERE conditions

Optional. The conditions that must be met for the records to be inserted.

Note

• When inserting records into a table using the Oracle INSERT statement, you must provide a value for every NOT NULL column.
• You can omit a column from the Oracle INSERT statement if the column allows NULL values.

Example - Using VALUES keyword

The simplest way to create an Oracle INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(5000, 'Apple');

This Oracle INSERT statement would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 5000 and a supplier_name of 'Apple'.

Example - Using SELECT statement

You can also create more complicated Oracle INSERT statements using SELECT statements.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following Oracle SELECT statement before performing the insert.

SELECT count(*)
FROM customers
WHERE customer_id > 5000;

Frequently Asked Questions

Question: I am setting up a database with clients. I know that you use the Oracle INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following Oracle INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

This Oracle INSERT statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following Oracle INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'CISCO', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

Question: How can I insert multiple rows of explicit data in one INSERT command in Oracle?

Answer: The following is an example of how you might insert 3 rows into the suppliers table in Oracle, using an Oracle INSERT statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'CISCO')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'GCP')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

Practice Exercise #1:

Based on the contacts table, insert a contact record whose contact_id is 1000, last_name is Smith, first_name is Jane, and address is 10 Somewhere St.:

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #1:

The following Oracle INSERT statement would insert this record into the employees table:

INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES
(1000, 'Smith', 'Jane', '10 Somewhere St.');

Practice Exercise #2:

Based on the contacts and customers table, insert into the contacts table all customers who reside in the state of 'Florida'.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE TABLE customers
( customer_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #2:

The following Oracle INSERT statement would insert this record into the suppliers table:

INSERT INTO contacts
(contact_id, last_name, first_name, address, city, state, zip_code)
SELECT customer_id, last_name, first_name, address, city, state, zip_code
FROM customers
WHERE state = 'Florida';

Since the number of fields in the contacts and customers table are the same and the fields are listed in the same order, you could write the solution as follows (though it is generally better practice to list the column names in case the table definitions change):

INSERT INTO contacts
SELECT *
FROM customers
WHERE state = 'Florida';