Skip Headers

Oracle® Database SQL Reference
10g Release 1 (10.1)

Part Number B10759-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page
Previous
Go to next page
Next
View PDF

INSERT

Purpose

Use the INSERT statement to add rows to a table, the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.

Additional Topics

Prerequisites

For you to insert rows into a table, the table must be in your own schema or you must have the INSERT object privilege on the table.

For you to insert rows into the base table of a view, the owner of the schema containing the view must have the INSERT object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the INSERT object privilege on the view.

If you have the INSERT ANY TABLE system privilege, then you can also insert rows into any table or the base table of any view.


Conventional and Direct-Path INSERT

You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT. When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader.

Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:

Syntax


insert::=
Description of insert.gif follows
Description of the illustration insert.gif

(single_table_insert ::=, multi_table_insert ::=)


single_table_insert ::=
Description of single_table_insert.gif follows
Description of the illustration single_table_insert.gif

(insert_into_clause ::=, values_clause ::=, returning_clause ::=, subquery::=)


insert_into_clause ::=
Description of insert_into_clause.gif follows
Description of the illustration insert_into_clause.gif

(DML_table_expression_clause::=)


values_clause ::=
Description of values_clause.gif follows
Description of the illustration values_clause.gif


returning_clause ::=
Description of returning_clause.gif follows
Description of the illustration returning_clause.gif


multi_table_insert ::=
Description of multi_table_insert.gif follows
Description of the illustration multi_table_insert.gif

(insert_into_clause ::=, values_clause ::=, conditional_insert_clause ::=, subquery::=)


conditional_insert_clause ::=
Description of conditional_insert_clause.gif follows
Description of the illustration conditional_insert_clause.gif

(insert_into_clause ::=, values_clause ::=)


DML_table_expression_clause::=
Description of DML_table_expression_clause.gif follows
Description of the illustration DML_table_expression_clause.gif

(subquery::=—part of SELECT, subquery_restriction_clause::=, table_collection_expression ::=)


subquery_restriction_clause::=
Description of subquery_restriction_clause.gif follows
Description of the illustration subquery_restriction_clause.gif


table_collection_expression ::=
Description of table_collection_expression.gif follows
Description of the illustration table_collection_expression.gif

Semantics


hint

Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.

For a multitable insert, if you specify the PARALLEL hint for any target table, then the entire multitable insert statement is parallelized even if the target tables have not been created or altered with PARALLEL specified. If you do not specify the PARALLEL hint, then the insert operation will not be parallelized unless all target tables were created or altered with PARALLEL specified.


See Also:



single_table_insert

In a single-table insert, you insert values into one row of a table, view, or materialized view by specifying values explicitly or by retrieving the values through a subquery.

You can use the flashback_query_clause in subquery to insert past data into table. Please refer to the flashback_query_clause of SELECT for more information on this clause.


Restriction on Single-table Inserts

If you retrieve values through a subquery, then the select list of the subquery must have the same number of columns as the column list of the INSERT statement. If you omit the column list, then the subquery must provide values for every column in the table.


insert_into_clause

Use the INSERT INTO clause to specify the target object or objects into which the database is to insert data.


DML_table_expression_clause

Use the INTO DML_table_expression_clause to specify the objects into which data is being inserted.

A set operator
a DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in Oracle Database Administrator's Guide