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)); 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)); 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 Tables | CREATE TABLE emp ( empno NUMBER(4) NOT NULL, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), sal 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, 800, 20); INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, 1600, 30); INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, 1250, 30); INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, 2975, 20); INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, 1250, 30); INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, 2850, 30); INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, 2450, 10); INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, 3000, 20); INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, 5000, 10); INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, 1500, 30); INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, 1100, 20); INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, 950, 30); INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, 3000, 60); INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, 1300, 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>; |
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; |
|
INSERT ALL WHEN |
Demo Insert ALL Variation | 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 INTO <table_name> VALUES <column_name_list) INTO <table_name> VALUES <column_name_list) ... <SELECT Statement>; |
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); |
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; |
|
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>; |
-- 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 | CREATE TABLE t ( scol VARCHAR2(20), dcol DATE); INSERT INTO t (scol, dcol) VALUES ('Test', TO_DATE('01-12-2007', '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; |