INSERT

Syntax

DML_table_expression_clause::=

subquery: see "SELECT and Subqueries".

with_clause::=

table_collection_expression::=

values_clause::=

returning_clause::=

Purpose

To add rows to a table, a view's base table, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or an object view's base table.

Prerequisites

For you to insert rows into a table, the table must be in your own schema or you must have INSERT 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 INSERT privilege on the base table. Also, if the view is in a schema other than your own, you must have INSERT privilege on the view.

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

Keywords and Parameters

hint	is a comment that passes instructions to the optimizer on choosing an execution plan for the statement. For the syntax and description of hints, see "Hints" and Oracle8i Designing and Tuning for Performance.
DML_table_expression_clause
schema	is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.
table | view | subquery	is the name of the table or object table, or view or object view, or the column or columns returned by a subquery, into which rows are to be inserted. If you specify a view or object view, Oracle inserts rows into the view's base table. If any value to be inserted is a REF to an object table, and if the object table has a primary key object identifier, then the column into which you insert the REF must be a REF column with a referential integrity or SCOPE constraint to the object table.
	If table (or the base table of view) contains one or more domain index columns, this statement executes the appropriate indextype insert routine. Issuing an INSERT statement against a table fires any INSERT triggers defined on the table. See Also: Oracle8i Data Cartridge Developer's Guide for more information on these routines
	Restrictions: You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED. With regard to the ORDER BY clause of the subquery in the DML_query_expression_clause, ordering is guaranteed only for the rows being inserted, and only within each extent of the table. Ordering of new rows with respect to existing rows is not guaranteed. If a view was created using the WITH CHECK OPTION, then you can insert into the view only rows that satisfy the view's defining query. If a view was created using a single base table, then you can insert rows into the view and then retrieve those values using the returning_clause.
	You cannot insert rows into a view except with INSTEAD OF triggers if the view's defining query contains one of the following constructs: - A set operator - A DISTINCT operator - An aggregate function - A GROUP BY, ORDER BY, CONNECT BY, or START WITH clause - A collection expression in a SELECT list - A subquery in a SELECT list - Joins (with some exceptions). See Oracle8i Administrator's Guide for details. If you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the INSERT statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE. See Also: "ALTER SESSION".
PARTITION (partition_name) | SUBPARTITION (subpartition_name)	specifies the name of the partition or subpartition within table (or the base table of view) targeted for inserts. If a row to be inserted does not map into a specified partition or subpartition, Oracle returns an error. Restriction: This clause is not valid for object tables or object views.
dblink	is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Schema Objects and Parts". You can insert rows into a remote table or view only if you are using Oracle's distributed functionality.
	If you omit dblink, Oracle assumes that the table or view is on the local database.
with_clause	restricts the subquery in one of the following ways:
	WITH READ ONLY specifies that the subquery cannot be updated.
	WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery. See the "WITH CHECK OPTION Example".
table_collection_expression	informs Oracle that the collection value expression should be treated as a table. See "Table Collection Examples".
	collection_expression	is a subquery that selects a nested table column from table or view.
	Note: In earlier releases of Oracle, table_collection_expression was expressed as "THE subquery". That usage is now deprecated.
column	is a column of the table or view. In the inserted row, each column in this list is assigned a value from the values_clause or the subquery.
	If you omit one of the table's columns from this list, the column's value for the inserted row is the column's default value as specified when the table was created. If any of these columns has a NOT NULL constraint, then Oracle returns an error indicating that the constraint has been violated and rolls back the INSERT statement. If you omit the column list altogether, the values_clause or query must specify values for all columns in the table. See Also: "CREATE TABLE" for more information on default column values.
values_clause	specifies a row of values to be inserted into the table or view. See the syntax description in "Expressions" and "SELECT and Subqueries". You must specify a value in the values_clause for each column in the column list. If you omit the column list, then the values_clause must provide values for every column in the table.
	Restrictions: You cannot initialize an internal LOB attribute in an object with a value other than empty or null. That is, you cannot use a literal. You cannot insert a BFILE value until you have initialized the BFILE locator to null or to a directory alias and filename. See the "BFILE Example". For information on initializing BFILEs, see Oracle Call Interface Programmer's Guide and Oracle8i Application Developer's Guide - Fundamentals.
	Note: If you insert string literals into a RAW column, during subsequent queries, Oracle will perform a full table scan rather than using any index that might exist on the RAW column.
subquery	is a subquery that returns rows that are inserted into the table. If the subquery selects no rows, Oracle inserts no rows into the table. When specified with VALUES, the subquery returns values to be inserted into one row. When specified without VALUES, the subquery can return values to be inserted into more than one row. The subquery can refer to any table, view, or snapshot, including the target table of the INSERT statement. The select list of this 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. See "SELECT and Subqueries".
	You can use subquery in combination with the TO_LOB function to convert the values in a LONG column to LOB values in another column in the same or another table. For a discussion of why and when to copy LONGs to LOBs, see Oracle8i Migration. For a description of how to use the TO_LOB function, see "Conversion Functions". See also the TO_LOB Example. To migrate LONGs to LOBs in a view, you must perform the migration on the base table, and then add the LOB to the view.
	Notes: If subquery returns (in part or totally) the equivalent of an existing materialized view, Oracle may use the materialized view (for query rewrite) in place of one or more tables specified in subquery. See Also: Oracle8i Data Warehousing Guide for more information on materialized views and query rewrite. If this subquery refers to remote objects, the INSERT operation can run in parallel as long as the reference does not loop back to an object on the local database. (See the description of the parallel_clause in "CREATE TABLE".) However, if the subquery in the DML_query_expression_clause refers to any remote objects, the INSERT operation will run serially without notification.
t_alias	provides a correlation name for the table, view, or subquery to be referenced elsewhere in the statement.
returning_clause	retrieves the rows affected by the INSERT. An INSERT statement with a returning_clause retrieves the rows inserted and stores them in PL/SQL variables or bind variables. Using a returning_clause in INSERT statements with a values_clause enables you to return column expressions, ROWIDs, and REFs and store them in output bind variables. You can also use INSERT with a returning_clause for views with single base tables.
	expr	is some form of the syntax descriptions in "Expressions". You must specify a column expression in the returning_clause for each variable in the data_item_list.
	INTO	indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item_list.
	data_item	is a PL/SQL variable or bind variable that stores a retrieved expr value.
	Restrictions: You cannot use this clause with parallel DML or with remote objects. You cannot retrieve LONG types with this clause. You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.

Examples

VALUES Examples

The following statement inserts a row into the DEPT table:

INSERT INTO dept   
   VALUES (50, 'PRODUCTION', 'SAN FRANCISCO'); 

The following statement inserts a row with six columns into the EMP table. One of these columns is assigned NULL and another is assigned a number in scientific notation:

INSERT INTO emp (empno, ename, job, sal, comm, deptno) 
   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40); 

The following statement has the same effect as the preceding example, but uses a subquery in the DML_query_expression_clause:

INSERT INTO (SELECT empno, ename, job, sal, comm, deptno FROM emp)
   VALUES (7890, 'JINKS', 'CLERK', 1.2E3, NULL, 40); 

Subquery Example

The following statement copies managers and presidents or employees whose commission exceeds 25% of their salary into the BONUS table:

INSERT INTO bonus 
   SELECT ename, job, sal, comm 
   FROM emp 
   WHERE comm > 0.25 * sal 
   OR job IN ('PRESIDENT', 'MANAGER'); 

Database Link Example

The following statement inserts a row into the ACCOUNTS table owned by the user SCOTT on the database accessible by the database link SALES:

INSERT INTO scott.accounts@sales (acc_no, acc_name) 
   VALUES (5001, 'BOWER'); 

Assuming that the ACCOUNTS table has a BALANCE column, the newly inserted row is assigned the default value for this column (if one has been defined), because this INSERT statement does not specify a BALANCE value.

Sequence Example

The following statement inserts a new row containing the next value of the employee sequence into the EMP table:

INSERT INTO emp 
   VALUES  (empseq.nextval, 'LEWIS', 'CLERK', 
            7902, SYSDATE, 1200, NULL, 20); 

Partition Example

The following example adds rows from LATEST_DATA into partition OCT98 of the SALES table:

INSERT INTO sales PARTITION (oct98)
   SELECT * FROM latest_data;

Bind Variable Example

The following example returns the values of the inserted rows into output bind variables :BND1 and :BND2:

INSERT INTO emp VALUES (empseq.nextval, 'LEWIS', 'CLARK',
                        7902, SYSDATE, 1200, NULL, 20) 
   RETURNING sal*12, job INTO :bnd1, :bnd2;

Bind Array Example

The following example returns the reference value for the inserted row into bind array :1:

INSERT INTO employee 
   VALUES ('Kitty Mine', 'Peaches Fuzz', 'Meena Katz')
   RETURNING REF(employee) INTO :1;

TO_LOB Example

The following example copies LONG data to a LOB column in the following existing table:

CREATE TABLE long_tab (long_pics LONG RAW);

First you must create a table with a LOB.

CREATE TABLE lob_tab (lob_pics BLOB);

Next, use an INSERT ... SELECT statement to copy the data in all rows for the LONG column into the newly created LOB column:

INSERT INTO lob_tab (lob_pics)
   SELECT TO_LOB(long_pics) FROM long_tab;

Once you are confident that the migration has been successful, you can drop the LONG_PICS table. Alternatively, if the table contains other columns, you can simply drop the LONG column from the table as follows:

ALTER TABLE long_tab DROP COLUMN long_pics;

BFILE Example

When you INSERT or UPDATE a BFILE, you must initialize it to null or to a directory alias and filename, as shown in the next example. Assume that the EMP table has a NUMBER column followed by a BFILE column:

INSERT INTO emp 
   VALUES (1, BFILENAME ('a_dir_alias', 'a_filename'));