INSERT Statement

The INSERT statement adds new rows of data to a specified database table. For a full description of the INSERT statement, see Oracle9i SQL Reference.

Syntax

Text description of the illustration insert_statement.gif

Keyword and Parameter Description

alias

This is another (usually short) name for the referenced table or view.

column_name[, column_name]...

This identifies a list of columns in a database table or view. Column names need not appear in the order in which they were defined by the CREATE TABLE or CREATE VIEW statement. However, no column name can appear more than once in the list. If the list does not include all the columns in a table, the missing columns are set to NULL or to a default value specified in the CREATE TABLE statement.

returning_clause

This clause lets you return values from inserted rows, thereby eliminating the need to SELECT the rows afterward. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the RETURNING clause for remote or parallel inserts. For the syntax of returning_clause, see "DELETE Statement".

sql_expression

This is any expression valid in SQL. For more information, see Oracle9i SQL Reference.

subquery

This is 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

This is a SELECT statement that returns a value or set of values. As many rows are added to the table as are returned by the subquery. It 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

This identifies 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, which must be a nested table. Operator TABLE informs Oracle that the value is a collection, not a scalar value.

VALUES (...)

This clause 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 only one value for each column in the column list. Also, 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');

Related Topics

SELECT INTO Statement