Oracle Insert Statements
Version 12.1.0.1

Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
 
Basic Inserts
Single Column Table Or View INSERT INTO <table_name>
(<column_name>)
VALUES
(<value>);
CREATE TABLE state (
state_abbrev VARCHAR2(2));

INSERT INTO state
(state_abbrev)
VALUES
('WA');

COMMIT;

SELECT * FROM state;
Multiple Column Table Or View - All Columns INSERT INTO <table_name>
VALUES
(<comma_separated_value_list>);
ALTER TABLE state
ADD (state_name VARCHAR2(30));

desc state

INSERT INTO state
(state_abbrev, state_name)
VALUES
('OR', 'Oregon');

COMMIT;

SELECT * FROM state;
Multiple Column Table Or View - Not All Columns INSERT INTO <table_name>
(<comma_separated_column_name_list>)
VALUES
(<comma_separated_value_list>);
RENAME state TO state_city;

ALTER TABLE state_city
ADD (city_name VARCHAR2(30));

desc state_city

INSERT INTO state_city
(state_abbrev, city_name)
VALUES
('CA', 'San Francisco');

COMMIT;

SELECT * FROM state_city;
Problem Not Specifying Column Names Demo INSERT INTO <table_name>
(<comma_separated_column_name_list>)
VALUES
(<comma_separated_value_list>);
desc state_city

INSERT INTO state_city
VALUES
('NV', 'Nevada', 'Las Vegas');

desc state_city
 
INSERT SELECT
Insert From SELECT statement INSERT INTO <table_name> <SELECT Statement>;
CREATE TABLE zip_new (
zip_code     VARCHAR2(5) NOT NULL,
state_abbrev VARCHAR2(2) NOT NULL,
city_name    VARCHAR2(30));

INSERT INTO zip_new
SELECT zip_code, state_abbrev, city_name
FROM postal_code;

SELECT * FROM zip_new;
 
RECORD INSERT
Insert Using A Record INSERT INTO <table_name>
VALUES <record_name>;
CREATE TABLE t AS
SELECT table_name, tablespace_name
FROM all_tables;

SELECT COUNT(*)
FROM t;

DECLARE
 trec  t%ROWTYPE;
BEGIN
  trec.table_name := 'NEW';
  trec.tablespace_name := 'NEW_TBSP';

  INSERT INTO t
  VALUES trec;

  COMMIT;
END;
/

SELECT COUNT(*) FROM t;
 
INSERT WHEN
Demo Table DDL CREATE TABLE emp (
empno    NUMBER(4) NOT NULL,
ename    VARCHAR2(10),
job      VARCHAR2(9),
mgr      NUMBER(4),
hiredate DATE,
sal      NUMBER(7,2),
comm     NUMBER(7,2),
deptno   NUMBER(2));

CREATE TABLE emp_10 AS SELECT * FROM emp WHERE 1=0;
CREATE TABLE emp_20 AS SELECT * FROM emp WHERE 1=0;
CREATE TABLE emp_30 AS SELECT * FROM emp WHERE 1=0;
CREATE TABLE leftover AS SELECT * FROM emp WHERE 1=0;
Demo Data INSERT INTO EMP VALUES
(7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-2012'), 1800, NULL, 20);
INSERT INTO EMP VALUES
(7499, 'SMALL', 'SALESMAN', 7698, TO_DATE('20-FEB-2014',), 2600, 300, 30);
INSERT INTO EMP VALUES
(7521,'MORGAN', SALESMAN', 7698, TO_DATE('22-FEB-2013'), 2250, 500, 30);
INSERT INTO EMP VALUES
(7566, 'NORGAARD', 'MANAGER', 7839, TO_DATE('01-APR-2011'), 3975, NULL, 20);
INSERT INTO EMP VALUES
(7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-SEP-2011'), 3250, 1400, 30);
INSERT INTO EMP VALUES
(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-MAY-2013'), 4850, NULL, 30);
INSERT INTO EMP VALUES
(7782, 'FOOTE', 'MANAGER', 7839, TO_DATE('09-JUN-2012'), 4450, NULL, 10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,TO_DATE('13-JUL-91')-85, 4000,NULL,20);
INSERT INTO EMP VALUES
(7839, 'ELLISON','PRESIDENT', NULL, TO_DATE('17-NOV-2010'), 50000, NULL, 10);
INSERT INTO EMP VALUES
(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-SEP-2012'), 3500, 0, 30);
INSERT INTO EMP VALUES
(7876, 'ADAMS', 'CLERK', 7788, TO_DATE('18-JUN-2011'), 3100, NULL, 20);
INSERT INTO EMP VALUES
(7900, 'JAMES', 'CLERK', 7698, TO_DATE('03-DEC-2011'), 2950, NULL, 30);
INSERT INTO EMP VALUES
(7902, 'LOFSTROM', 'ANALYST', 7566, TO_DATE('04-DEC-2012'), 4000, NULL, 20);
INSERT INTO EMP VALUES
(7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-JAN-2012'), 2300, NULL, 10);
COMMIT;
Demo Insert Statement INSERT
WHEN (<condition>) THEN
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
WHEN (<condition>) THEN
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
ELSE
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
SELECT <column_list> FROM <table_name>;
INSERT
WHEN (deptno=10) THEN
  INTO emp_10 (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno=20) THEN
  INTO emp_20 (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno=30) THEN
  INTO emp_30 (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
ELSE
  INTO leftover (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
SELECT * FROM emp;

SELECT * FROM emp_10;
SELECT * FROM emp_20;
SELECT * FROM emp_30;
SELECT * FROM leftover;
 
INSERT ALL
Without the WHEN clause INSERT ALL performs all inserts unconditionally INSERT ALL
INTO <table_name> VALUES <column_name_list)
INTO <table_name> VALUES <column_name_list)
...
<SELECT Statement>;
conn uwclass/uwclass

CREATE TABLE ap_cust (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

CREATE TABLE ap_orders (
order_date DATE,
program_id VARCHAR2(3));

INSERT ALL
INTO ap_cust VALUES (customer_id, program_id, delivered_date)
INTO ap_orders VALUES (order_date, program_id)
SELECT program_id, delivered_date, customer_id, order_date
FROM airplanes;

SELECT * FROM ap_cust
WHERE rownum < 1001;

SELECT * FROM ap_orders
WHERE rownum < 1001;
CREATE TABLE t (
pid   NUMBER(5),
fname VARCHAR2(20),
lname VARCHAR2(25));

INSERT ALL
INTO t (pid, fname, lname)
VALUES (1, 'Dan', 'Morgan')
INTO t (pid, fname, lname)
VALUES (2, 'Jeremiah', 'Wilton')
INTO t (pid, fname, lname)
VALUES (3, 'Helen', 'Lofstrom')
SELECT * FROM dual;

SELECT * FROM t;
Another INSERT ALL Demo DECLARE
  c CLOB := '<ORDER_ROWS>
             <ORDER>
               <ORDER_ID>1</ORDER_ID>
               <ORDER_REVISION>1</ORDER_REVISION>
               <ORDER_DATE>06-MAR-2014</ORDER_DATE>
               <ORDER_HEADER_HASH>1357908642</ORDER_HEADER_HASH>
               <ORDER_CURRENCY>USD</ORDER_CURRENCY>
               <ORDER_LANGUAGE>ENG</ORDER_LANGUAGE>
               <MACHINE_NAME>MachName</MACHINE_NAME>
               <ASSEMBLY_NAME>Assembly Name</ASSEMBLY_NAME>
               <USER_NAME>Dan Morgan</USER_NAME>
               <CALLING_METH_NAME>Test Method</CALLING_METH_NAME>
               <OH_ALT_ID_HASH>1357908642</OH_ALT_ID_HASH>
               <SITE_ID>1</SITE_ID>
               <IS_PLACED>1</IS_PLACED>
               <IS_PLACED_SUCCESS>1</IS_PLACED_SUCCESS>
               <IS_ROUTED>0</IS_ROUTED>
               <IS_ROUTED_SUCCESS>0</IS_ROUTED_SUCCESS>
               <IS_SHIPPED>0</IS_SHIPPED>
               <IS_CLOSED>0</IS_CLOSED>
               <PLACED_DATE>07-MAR-2014</PLACED_DATE>
               <MEMBER_ID>1</MEMBER_ID>
               <HANDLING_METHOD>HAND METH</HANDLING_METHOD>
               <FULFILLMENT_METHOD>FULFILL METH</FULFILLMENT_METHOD>
               <TOTALS_HASH>100</TOTALS_HASH>
             </ORDER>
           </ORDER_ROWS>';
BEGIN
  INSERT ALL
    INTO orders
    VALUES (order_id, order_revision, order_date, order_header_hash, order_currency,
            order_language, machine_name, assembly_name, user_name, calling_meth_name)
    INTO order_header
    VALUES (order_header_hash, oh_alt_id_hash, site_id, is_placed, is_placed_success,
            is_routed, is_routed_success, is_shipped, is_closed, placed_date, member_id,
            handling_method, fulfillment_method, totals_hash)
  WITH t AS (SELECT xmltype(c) xml FROM dual)
  SELECT
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/ORDER_ID')) order_id,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/ORDER_REVISION')) order_revision,
    TO_DATE(extractvalue(t2.column_value, 'ORDER/ORDER_DATE')) order_date,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/ORDER_HEADER_HASH')) order_header_hash,
    (extractvalue(t2.column_value, 'ORDER/ORDER_CURRENCY')) order_currency,
    (extractvalue(t2.column_value, 'ORDER/ORDER_LANGUAGE')) order_language,
    (extractvalue(t2.column_value, 'ORDER/MACHINE_NAME')) machine_name,
    (extractvalue(t2.column_value, 'ORDER/ASSEMBLY_NAME')) assembly_name,
    (extractvalue(t2.column_value, 'ORDER/USER_NAME')) user_name,
    (extractvalue(t2.column_value, 'ORDER/CALLING_METH_NAME')) calling_meth_name,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/OH_ALT_ID_HASH')) oh_alt_id_hash,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/SITE_ID')) site_id,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/IS_PLACED')) is_placed,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/IS_PLACED_SUCCESS')) is_placed_success,
    (extractvalue(t2.column_value, 'ORDER/IS_ROUTED')) is_routed,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/IS_ROUTED_SUCCESS')) is_routed_success,
    (extractvalue(t2.column_value, 'ORDER/IS_SHIPPED')) is_shipped,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/IS_CLOSED')) is_closed,
    TO_DATE(extractvalue(t2.column_value, 'ORDER/PLACED_DATE')) placed_date,
    (extractvalue(t2.column_value, 'ORDER/MEMBER_ID')) member_id,
    (extractvalue(t2.column_value, 'ORDER/HANDLING_METHOD')) handling_method,
    (extractvalue(t2.column_value, 'ORDER/FULFILLMENT_METHOD')) fulfillment_method,
    TO_NUMBER(extractvalue(t2.column_value, 'ORDER/TOTALS_HASH')) totals_hash
  FROM t t, TABLE(xmlsequence(t.xml.extract('ORDER_ROWS/ORDER'))) t2;
END;
/
 
INSERT ALL WHEN
Demo Insert ALL Variation

And thank you to Ranit Biswas for catching a typo in this section.
INSERT ALL
WHEN (<condition>) THEN
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
WHEN (<condition>) THEN
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
ELSE
  INTO <table_name> (<column_list>)
  VALUES (<values_list>)
SELECT <column_list> FROM <table_name>;
TRUNCATE TABLE emp_10;
TRUNCATE TABLE emp_20;
TRUNCATE TABLE emp_30;
TRUNCATE TABLE leftover;

INSERT ALL
WHEN
(deptno=10) THEN
  INTO emp_10 (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno=20) THEN
  INTO emp_20 (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
WHEN (deptno<=30) THEN
  INTO emp_30 (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
ELSE
  INTO leftover (empno,ename,job,mgr,sal,deptno)
  VALUES (empno,ename,job,mgr,sal,deptno)
SELECT * FROM emp;

SELECT * FROM emp_10;
SELECT * FROM emp_20;
SELECT * FROM emp_30;
SELECT * FROM leftover;
 
INSERT FIRST WHEN
The WHEN clause is evaluated in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row INSERT FIRST
WHEN <condition> THEN
INTO <table_name> VALUES <column_name_list)
INTO <table_name> VALUES <column_name_list)
...
<SELECT Statement>;
conn uwclass/uwclass

CREATE TABLE cust_ah (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

CREATE TABLE cust_ip (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

CREATE TABLE cust_qz (
customer_id VARCHAR2(4),
program_id VARCHAR2(3),
del_date DATE);

INSERT FIRST
WHEN customer_id < 'I' THEN
 INTO cust_ah
  VALUES (customer_id, program_id, delivered_date)
WHEN customer_id < 'Q' THEN
 INTO cust_ip
  VALUES (customer_id, program_id, delivered_date)
WHEN customer_id > 'PZZZ' THEN
 INTO cust_qz
  VALUES (customer_id, program_id, delivered_date)
SELECT program_id, delivered_date, customer_id, order_date
FROM airplanes;

SELECT customer_id, COUNT(*)
FROM cust_ah
GROUP BY customer_id;

SELECT customer_id, COUNT(*)
FROM cust_ip
GROUP BY customer_id;

SELECT customer_id, COUNT(*)
FROM cust_qz
GROUP BY customer_id;
 
INSERT WITH CHECK OPTION
Note: Use WITH CHECK OPTION to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery
CHECK OPTION demo INSERT INTO (
<SQL_statement> WITH CHECK OPTION)
VALUES
(value_list);
conn uwclass/uwclass

CREATE TABLE dept (
deptno NUMBER(2),
dname  VARCHAR2(15),
loc    VARCHAR2(15));

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

SELECT * FROM dept;

INSERT INTO (
SELECT deptno, dname, loc
FROM dept
WHERE deptno < 30)
VALUES (98, 'TRAVEL', 'SEATTLE');

SELECT * FROM dept;

INSERT INTO (
SELECT deptno, dname, loc
FROM dept
WHERE deptno < 30 WITH CHECK OPTION)
VALUES (99, 'TRAVEL', 'SEATTLE');

SELECT * FROM dept;
 
Anonymous Block Insert
Demo Insert Statement BEGIN
   <INSERT Statements>
END;
/
TRUNCATE TABLE zip_new;

-- copy the following 10 lines into SQL*Plus as is:
INSERT INTO zip_new
VALUES ('98101', 'WA', 'Seattle');
INSERT INTO zip_new
VALUES ('98004', 'WA', 'Bellevue');
INSERT INTO zip_new
VALUES ('98040', 'WA', 'Mercer Is.');
INSERT INTO zip_new
VALUES ('98072', 'WA', 'Woodinville');
INSERT INTO zip_new
VALUES ('98065', 'CA', 'Los Angeles');

SELECT * FROM zip_new;

ROLLBACK;

BEGIN
  INSERT INTO zip_new
  VALUES ('98101', 'WA', 'Seattle');
  INSERT INTO zip_new
  VALUES ('98004', 'WA', 'Bellevue');
  INSERT INTO zip_new
  VALUES ('98040', 'WA', 'Mercer Is.');
  INSERT INTO zip_new
  VALUES ('98072', 'WA', 'Woodinville');
  INSERT INTO zip_new
  VALUES ('98065', 'CA', 'Los Angeles');
END;
/


SELECT * FROM zip_new;
 
Inserting Into A SELECT Statement
Demo Insert Statement INSERT INTO (<SELECT Statement>);
SELECT empno, ename, job, sal, deptno
FROM emp;

INSERT INTO (
  SELECT empno, ename, job, sal, deptno
  FROM emp)

VALUES
(1, 'MORGAN', 'DBA', '1', 40);

SELECT * FROM emp;
Insert using a Common Table Expression (WITH Clause)

The Library thanks Oracle ACE Director Lucas Jellema for this contribution very slightly rewritten to match library format.

Note: This statement will crash a session in unpatched 11.2.0.3 due to a bug but works in other tested versions.
CREATE TABLE seats (
rid      NUMBER(3),
roww     NUMBER(3) NOT NULL,
position VARCHAR2(1) NOT NULL);

ALTER TABLE seats
ADD CONSTRAINT pk_seats
PRIMARY KEY (rid);

INSERT INTO seats
(rid, roww, position)
WITH q_positions AS (
  SELECT CHR(64+level) position
  FROM dual
  CONNECT BY level < 7),
     q_rowws AS (
  SELECT level roww
  FROM dual
  CONNECT BY level < 47)
SELECT rownum, roww, position
FROM q_positions CROSS JOIN q_rowws;
 
Create Table INSERT (CTAS)
Demo Table Creation Insert CREATE TABLE <table_name> AS <SELECT Statement>;
CREATE TABLE servers_bak AS
SELECT *
FROM servers;

desc servers_bak

SELECT COUNT(*)
FROM servers_bak;
 
INSERT With Returning Clause
Demo Insert With Returning Clause INSERT INTO <table_name>
(column_list)
VALUES
(values_list)
RETURNING <value_name>
INTO <variable_name>;
conn uwclass/uwclass

-- Use emp table from INSERT-WHEN demo above


CREATE SEQUENCE seq_emp;

set serveroutput on

DECLARE
 x emp.empno%TYPE;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING empno
  INTO x;

  dbms_output.put_line(x);
END;
/

DECLARE
 r rowid;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING rowid
  INTO r;

  dbms_output.put_line(r);
END;
/

DECLARE
 x emp.empno%TYPE;
 r rowid;
BEGIN
  INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING rowid, empno
  INTO r, x;

  dbms_output.put_line(r);
  dbms_output.put_line(x);
END;
/

/

/

/
 
Inserting Dates
Date Format Specification conn uwclass/uwclass

CREATE TABLE t (
scol VARCHAR2(20),
dcol DATE);

INSERT INTO t
(scol, dcol)
VALUES
('Test', TO_DATE('01-12-2014', 'MM-DD-YYYY'));

SELECT * FROM t;

INSERT INTO t
(scol, dcol)
VALUES
('Test', TO_DATE('01-12-2007', 'DD-MM-YYYY'));

SELECT * FROM t;

INSERT INTO t
(scol, dcol)
VALUES
('Test', DATE '2007-07-01');

SELECT * FROM t;

Related Topics
Database Link
Delete Statements
Error Logging
Merge Statements
Select Statements
Tables
Types
Update Statements
Views

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved