INSERT Statement

Add data to a table, view, or snapshot.

Syntax:

   INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...)
      What_to_insert 

   INSERT [hint] INTO [schema.] table
      [[SUB]PARTITION (ptn_name)] [t_alias] (column, column,...)
         What_to_insert 

   INSERT [hint] INTO subquery 
      WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ]
         [t_alias] (column, column,...)
            What_to_insert 

Key:
  What_to_insert: 
 
     VALUES ([expr, expr...])
         [RETURNING expr, expr... INTO host_variable|plsql_variable]
 or
     SubQuery [RETURNING expr, expr... INTO host_variable|plsql_variable]

In the syntax above "table" can be replaced with "view" or "snapshot".

The VALUES keyword is required when directly inserting data, rather than using a subquery.

Examples:

CREATE TABLE People(
 surname VARCHAR2(25),
 firstname   VARCHAR2(25)
 );

INSERT INTO People (surname,firstname)
 VALUES('Smith','John');

INSERT INTO People VALUES('McKay','Alice');

INSERT INTO People(firstname, surname) VALUES('Jill','Jones'); 

INSERT INTO People(firstname, surname) (select emp_first, emp_surname from Employees);

Copy specific columns (and rows) from one table to another:

CREATE TABLE Postable_Table
    (p_code        VARCHAR2(6) NOT NULL,
     p_description VARCHAR2(30),
     p_tax_code    VARCHAR2(2));


INSERT INTO Postable_Table
  SELECT distinct
    cs_cost_centre,'extracted data',NULL
  FROM 
    Cost_Centre_Table,
  WHERE cs_postable='Y';

"When debugging, novices insert corrective code; experts remove defective code" - Richard Pattis

Related Commands:

DELETE
EXPLAIN PLAN
SELECT
TRUNCATE
UPDATE

Related Views:

 DBA_SNAPSHOTS        ALL_SNAPSHOTS        USER_SNAPSHOTS
 DBA_ALL_TABLES       ALL_ALL_TABLES       USER_ALL_TABLES
 DBA_TABLES           ALL_TABLES           USER_TABLES
     TAB
 DBA_VIEWS            ALL_VIEWS            USER_VIEWS
 DICTIONARY
 DICT_COLUMNS

Equivalent SQL Server command:

INSERT