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 can be replaced with a sub query.
The VALUES clause can 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 Oracle Commands:
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