INSERT Statement

The INSERT statement adds one or more new rows of data to a database table. For a full description of the INSERT statement, see Oracle Database SQL Reference.

Syntax

Description of insert_statement.gif follows

Description of the illustration insert_statement.gif

Keyword and Parameter Description

alias

Another (usually short) name for the referenced table or view.

column_name[, column_name]...

A list of columns in a database table or view. The columns can be listed in any order, as long as the expressions in the VALUES clause are listed in the same order. Each column name can only be listed once. If the list does not include all the columns in a table, each missing columns is set to NULL or to a default value specified in the CREATE TABLE statement.

returning_clause

Returns values from inserted rows, eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables or into collections. You cannot use the RETURNING clause for remote or parallel inserts. If the statement does not affect any rows, the values of the variables specified in the RETURNING clause are undefined. For the syntax of returning_clause, see "DELETE Statement".

sql_expression

Any expression valid in SQL. For example, it could be a literal, a PL/SQL variable, or a SQL query that returns a single value. For more information, see Oracle Database SQL Reference. PL/SQL also lets you use a record variable here.

subquery

A SELECT statement that provides a set of rows for processing. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement".

subquery3

A SELECT statement that returns a set of rows. Each row returned by the select statement is inserted into the table. The subquery must return a value for every column in the column list, or for every column in the table if there is no column list.

table_reference

A table or view that must be accessible when you execute the INSERT statement, and for which you must have INSERT privileges. For the syntax of table_reference, see "DELETE Statement".

TABLE (subquery2)

The operand of TABLE is a SELECT statement that returns a single column value representing a nested table. This operator specifies that the value is a collection, not a scalar value.

VALUES (...)

Assigns the values of expressions to corresponding columns in the column list. If there is no column list, the first value is inserted into the first column defined by the CREATE TABLE statement, the second value is inserted into the second column, and so on. There must be one value for each column in the column list. The datatypes of the values being inserted must be compatible with the datatypes of corresponding columns in the column list.

Usage Notes

Character and date literals in the VALUES list must be enclosed by single quotes ('). Numeric literals are not enclosed by quotes.

The implicit cursor SQL and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an INSERT statement.

Examples

The following examples show various forms of INSERT statement:

INSERT INTO bonus SELECT ename, job, sal, comm FROM emp
   WHERE comm > sal * 0.25;
...
INSERT INTO emp (empno, ename, job, sal, comm, deptno)
   VALUES (4160, 'STURDEVIN', 'SECURITY GUARD', 2045, NULL, 30);
...
INSERT INTO dept
   VALUES (my_deptno, UPPER(my_dname), 'CHICAGO');