Insert

From Oracle FAQ
Jump to: navigation, search

Insert is a SQL DML command used to add data (rows) to a table.

Examples[edit]

Insert a single row into a table:

INSERT INTO dept VALUES (10, 'pre-sales', 'New York');

Insert rows from one table into another table:

INSERT INTO emp2 SELECT * FROM emp;
CREATE TABLE emp3 AS SELECT * FROM emp;

Insert multiple rows into a table with a single statement:

INSERT ALL
  INTO dept (deptno, dname, loc) VALUES (11, 'Sales', 'New York')
  INTO dept (deptno, dname, loc) VALUES (12, 'Pre-sales', 'Dallas')
SELECT 1 FROM dual;
INSERT INTO dept
  SELECT 13, 'Sales', 'New York'   FROM dual UNION
  SELECT 14, 'Pre-sales', 'Dallas' FROM dual;

Insert multiple rows into different tables with a single statement:

INSERT ALL
   WHEN type=1 THEN INTO tab1 VALUES (myseq.NEXTVAL, val)
   WHEN type=2 THEN INTO tab2 VALUES (myseq.NEXTVAL, val)
   WHEN type IN (3,4,5) THEN INTO tab3 VALUES (myseq.NEXTVAL, val)
   ELSE INTO tab4 VALUES (myseq.NEXTVAL, val)
SELECT type, val FROM source_tab;

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #