PL/SQL User's Guide and Reference

INSERT Statement

Description

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

Syntax

insert_statement ::=

INSERT INTO {table_reference | (subquery)}
   [(column_name[, column_name]...)]
   {VALUES (sql_expression[, sql_expression]...) | subquery};

table_reference ::=

[schema_name.]{table_name | view_name}[@dblink_name]

Keyword and Parameter Description

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.

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.

sql_expression

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

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. The first value is associated with the first column, the second value is associated with the second column, and so on. If there is no column list, you must supply a value for each column in the table.

The datatypes of the values being inserted must be compatible with the datatypes of corresponding columns in the column list. For more information, see "Datatypes" .

subquery

This is a select statement that provides a value or set of values to the INSERT statement. The syntax of subquery is like the syntax of select_into_statement defined in "SELECT INTO Statement"

, except that subquery cannot have an INTO clause.

As many rows are added to the table as are returned by the subquery in the VALUES clause. 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.

Usage Notes

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

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

An INSERT statement might insert one or more rows or no rows. If one or more rows are inserted, you get the following results:

SQL%NOTFOUND yields FALSE

SQL%FOUND yields TRUE

SQL%ROWCOUNT yields the number of rows inserted

If no rows are inserted, you get these results:

SQL%NOTFOUND yields TRUE

SQL%FOUND yields FALSE

SQL%ROWCOUNT yields 0

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');