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 table_name (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:

DELETE - Delete rows
EXPLAIN PLAN
MERGE - Select rows for update or insert into a table.
SELECT
TRUNCATE - Empty table
UPDATE - Update rows

 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


© Copyright SS64.com 1999-2013
Some rights reserved