INSERT Statement

Add data to a table, view, or snapshot.

Syntax:

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

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

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

   INSERT
      WHEN (condition) THEN
        INTO table (column, column,...)
        VALUES (expr, expr...)
      WHEN (condition) THEN
        INTO table (column, column,...)
        VALUES (expr, expr...)
      ELSE
        INTO  (column, column,...)
        VALUES (expr, expr...)

In the syntax above, the VALUES clause may be replaced with a sub query.
The VALUES clause may also be followed by a RETURNING clause to assign the results to a variable:

       [RETURNING expr, expr... INTO host_variable | plsql_variable]

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

Examples:

SQL> Insert into demotable (col1, col2, col3)
                 values (val1, val2, val3);

Inserting values into three columns:
SQL> Insert INTO staff (id, surname,firstname) VALUES(123, 'Smith','John');

The column names can be omitted if the values are listed in the same order as the table columns, (obviously this can break if extra columns are added to the table later on):
SQL> Insert INTO staff VALUES(123, 'Smith','John');

Insert using a subquery:

SQL> Insert into staff(firstname, surname) (Select emp_first, emp_surname From employees);

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

Insert into postables
  (postable_id, postable_name, dept, auth_name) 
VALUES( 
  (Select postables_seq.nextval),
  (Select 'some static text' postable_name),
  (Select dept      From cost_centres Where cc_code = 123), 
  (Select auth_name From authorisers  Where auth_code = 'Z5f')
)

-- The same as above written to select alias names:

insert into postables
  (postable_id, postable_name, dept, auth_name)
  (select AA,BB,CC,DD 
  from
    (Select postables_seq.nextval AA), 
    (Select 'some static text'    BB),
    (Select dept                  CC From cost_centres Where cc_code = 123),
    (Select auth_name             DD From authorisers  Where auth_code = 'Z5f'));

"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