INSERT

Purpose

Use the INSERT statement 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.

Additional Topics

• Prerequisites

• Syntax

• Keywords and Parameters

• Examples

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.

Syntax

DML_table_expression_clause::=

subquery: see SELECT and subquery.

with_clause::=

table_collection_expression::=

values_clause::=

returning_clause::=

Keywords and Parameters

hint

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

See Also: "Hints" and Oracle8i Performance Guide and Reference for the syntax and description of hints

DML_table_expression_clause

schema	Specify 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	Specify 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
PARTITION (partition_name) | SUBPARTITION (subpartition_name)	Specify 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	Specify a complete or partial name of a database link to a remote database where the table or view is located. 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. See Also: "Syntax for Schema Objects and Parts in SQL Statements"for information on referring to database links

Restrictions on the DML_table_expression_clause:

• 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 or analytic 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

with_clause

Use the with_clause to restrict 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 Also: "WITH CHECK OPTION Example"

table_collection_expression

Use the table_collection_expression to inform Oracle that the collection value expression should be treated as a table.

See Also: "Table Collection Examples"

collection_expression	Specify 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.

t_alias

Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement.

column

Specify 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

Specify a row of values to be inserted into the table or view. 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 Also: "Inserting into a BFILE Example" Oracle Call Interface Programmer's Guide and Oracle8i Application Developer's Guide - Fundamentals for information on initializing BFILEs "Expressions" and SELECT and subquery for syntax of valid expressions

  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.

returning_clause

The returning clause retrieves the rows affected by a DML (INSERT, UPDATE, or DELETE) statement. You can specify this clause for tables and snapshots, and for views with a single base table.

• When operating on a single row, a DML statement with a returning_clause can retrieve column expressions using the affected row, rowid, and REFs to the affected row and store them in host variables or PL/SQL variables.

• When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs involving the affected rows in bind arrays.

expr	Each item in the expr list must be a valid expression syntax.
INTO	The INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item list.
data_item	Each data_item is a host variable or PL/SQL variable that stores the retrieved expr value.

For each expression in the RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO list.

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.

  See Also: PL/SQL User's Guide and Reference for information on using the BULK COLLECT clause to return multiple values to collection variables

subquery

Specify 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 without VALUES, the subquery can return zero or more rows, which are then inserted.

• When specified with VALUES, the subquery must be a scalar subquery. That is, it must return exactly one row with one value.

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.

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. 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.

See Also: "Conversion Functions" Oracle8i Migration for a discussion of why and when to copy a LONG to a LOB Inserting with TO_LOB Example for a description of how to use the TO_LOB function SELECT and subquery

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. However, if the subquery in the DML_query_expression_clause refers to any remote objects, the INSERT operation will run serially without notification. See Also: parallel_clause in CREATE TABLE

Examples

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

Inserting Values with a 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'); 

Inserting into a Remote Database 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.

Inserting Sequence Values 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); 

Inserting into a 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;

Inserting Using Bind Variables 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;

Returning Values into a 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;

Inserting with 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;

Inserting into a 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'));