|Oracle9i SQL Reference
Release 1 (9.0.1)
Part Number A90125-01
DROP SEQUENCE to ROLLBACK, 14 of 20
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.
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
TABLE system privilege, you can also insert rows into any table or any view's base table.
You can use the
INSERT statement to insert data into a table, partition, or view in two ways: conventional
INSERT and direct-path
INSERT. When you issue a conventional
INSERT statement, Oracle reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path
INSERT, Oracle appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of Oracle's direct-path loader utility, SQL*Loader.
INSERT is subject to a number of restrictions. If any of these restrictions is violated, Oracle executes conventional
INSERT serially without returning any message (unless otherwise noted):
INSERTstatements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
INSERTstatement, but not after it.
INSERTin the same transaction, Oracle returns an error and rejects the statement.
ROW_LOCKINGinitialization parameter cannot be set to
INSERTstatement cannot be or become distributed.
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
For a multitable insert, if you specify the
PARALLEL hint for any target table, the entire multitable insert statement is parallelized even if the target tables have not been created or altered with
PARALLEL specified. If you do not specify the
PARALLEL hint, the insert operation will not be parallelized unless all target tables were created or altered with
In a single-table insert, you insert values into one row of a table, view, or materialized view by specifying values explicitly or by retrieving the values through a subquery.
Restriction: If you retrieve values through a subquery, the select list of the 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.
INTO clause to specify the target object or objects into which Oracle is to insert data.
INTO dml_table_expression_clause to specify the objects into which data is being inserted.
Specify the schema containing the table, view, or materialized view. If you omit schema, Oracle assumes the object is in your own schema.
Specify the name of the table or object table, view or object view, materialized 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.
INSERT statement against a table fires any
INSERT triggers defined on the table.
Oracle9i Data Cartridge Developer's Guide for more information on these routines
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.
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.
"Syntax for Schema Objects and Parts in SQL Statements"for information on referring to database links
Use the subquery_restriction_clause to restrict the subquery in one of the following ways:
ONLYspecifies that the subquery cannot be updated.
OPTIONspecifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery.
The table_collection_expression lets you inform Oracle that the value of collection_expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a built-in function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting.
Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement.
Restriction: You cannot specify t_alias during a multitable insert.
BYclause of the subquery in the dml_table_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.
OPTION, then you can insert into the view only rows that satisfy the view's defining query.
OFtriggers if the view's defining query contains one of the following constructs:
INSERTstatement will fail unless the
SKIP_UNUSABLE_INDEXESsession parameter has been set to
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 or last altered. If any of these columns has a
NULL constraint, then Oracle returns an error indicating that the constraint has been violated and rolls back the
If you omit the column list altogether, the values_clause or query must specify values for all columns in the table.
CREATE TABLE for more information on default column values
For a single-table insert operation, 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.
For a multitable insert operation, each expression in the values_clause must refer to columns returned by the select list of the subquery. If you omit the values_clause, the select list of the subquery determines the values to be inserted, so it must have the same number of columns as the column list of the corresponding insert_into_clause. If you do not specify a column list in the insert_into_clause, the computed row must provide values for all columns in the target table.
For both types of insert operations, if you specify a column list in the insert_into_clause, then Oracle assigns to each column in the list a corresponding value from the values clause or the subquery. You can specify
DEFAULT for any value in the values_clause. If you have specified a default value for the corresponding column of the table or view, that value is inserted. If no default value for the corresponding column has been specified, Oracle inserts null.
BFILEvalue until you have initialized the
BFILElocator to null or to a directory alias and filename.
DEFAULTwhen inserting into a view.
The returning clause retrieves the rows affected by a DML (
DELETE) statement. You can specify this clause for tables and materialized views, 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.
Each item in the
expr list must be a valid expression syntax. All forms are valid except scalar subquery expressions.
INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in
data_item is a host variable or PL/SQL variable that stores the retrieved
For each expression in the
RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the
returning_clausefor a multitable insert.
LONGtypes with this clause.
OFtrigger has been defined.
In a multitable insert, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more tables.
ALL followed by multiple insert_into_clauses to perform an unconditional multitable insert. Oracle executes each insert_into_clause once for each row returned by the subquery.
Specify the conditional_insert_clause to perform a conditional multitable insert. Oracle filters each insert_into_clause through the corresponding
WHEN condition, which determines whether that insert_into_clause is executed. A single multitable insert statement can contain up to 127
If you specify
ALL, Oracle evaluates each
WHEN clause regardless of the results of the evaluation of any other
WHEN clause. For each
WHEN clause whose condition evaluates to true, Oracle executes the corresponding
INTO clause list.
If you specify
FIRST, Oracle evaluates each
WHEN clause in the order in which it appears in the statement. For the first
WHEN clause that evaluates to true, Oracle executes the corresponding
INTO clause and skips subsequent
WHEN clauses for the given row.
For a given row, if no
WHEN clause evaluates to true:
ELSEclause Oracle executes the
INTOclause list associated with the
Specify a subquery that returns rows that are inserted into the table. The subquery can refer to any table, view, or materialized view, including the target tables of the
INSERT statement. If the subquery selects no rows, Oracle inserts no rows into 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.
The following statement inserts a row into the sample table
If the departments table had been created with a default value of 121 for the
manager_id column, you could issue the same statement as follows:
The following statement inserts a row with six columns into the
employees table. One of these columns is assigned
NULL and another is assigned a number in scientific notation:
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary, commission_pct) VALUES (207, 'Gregory', 'email@example.com', sysdate, 'PU_CLERK', 1.2E3, NULL);
The following statement has the same effect as the preceding example, but uses a subquery in the dml_table_expression_clause:
INSERT INTO (SELECT employee_id, last_name, email, hire_date, job_id, salary, commission_pct FROM employees) VALUES (207, 'Gregory', 'firstname.lastname@example.org', sysdate, 'PU_CLERK', 1.2E3, NULL);
The following statement copies employees whose commission exceeds 25% of their salary into the
bonuses table (which is created in "MERGE Example"):
INSERT INTO bonuses SELECT employee_id, salary*1.1 FROM employees WHERE commission_pct > 0.25 * salary;
The following statement inserts a row into the
accounts table owned by the user
scott on the database accessible by the database link
If 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
The following statement inserts a new row containing the next value of the departments sequence into the
The following example adds rows from
latest_data into partition
oct98 of the
The following example returns the values of the inserted rows into output bind variables :
bnd1 and :
bnd2. (The bind variables must first be declared.)
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary) VALUES (employees_seq.nextval, 'Doe', 'email@example.com', SYSDATE, 'SA_CLERK', 2400) RETURNING salary*12, job_id INTO :bnd1, :bnd2;
The following example returns the reference value for the inserted row into bind array :
INSERT INTO employee VALUES ('Kitty Mine', 'Peaches Fuzz', 'Meena Katz') RETURNING REF(employee) INTO :1;
The following example inserts into the
persons table, which is created in "Substitutable Table and Column Examples". The first statement uses the root type
person_t. The second insert uses the
person_t, and the third insert uses the
part_time_emp_t subtype of
INSERT INTO persons VALUES (person_t('Bob', 1234)); INSERT INTO persons VALUES (employee_t('Joe', 32456, 12, 100000)); INSERT INTO persons VALUES ( part_time_emp_t('Tim', 5678, 13, 1000, 20));
The following example inserts into the
books table, which was created in "Substitutable Table and Column Examples". Notice that specification of the attribute values is identical to that for the substitutable table example:
INSERT INTO books VALUES ( 'An Autobiography', person_t('Bob', 1234)); INSERT INTO books VALUES ( 'Business Rules', employee_t('Joe', 3456, 12, 10000)); INSERT INTO books VALUES ( 'Mixing School and Work', part_time_emp_t('Tim', 5678, 13, 1000, 20));
You can extract data from substitutable tables and columns using built-in functions and conditions. For examples, see the functions
, and "IS OF type Conditions".
The following example copies
LONG data to a LOB column in the following
First you must create a table with a LOB.
Next, use an
SELECT statement to copy the data in all rows for the
LONG column into the newly created LOB column:
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:
When you insert into 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
The following example uses the multitable insert syntax to insert into the sample table
sh.sales some data from an input table with a different structure.
The input table looks like this:
PRODUCT_ID CUSTOMER_ID WEEKLY_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT ---------- ----------- --------- ---------- ---------- ---------- -------------------- ---------- ---------- 111 222 01-OCT-00 100 200 300 400 500 600 700 222 333 08-OCT-00 200 300 400 500 600 700 800 333 444 15-OCT-00 300 400 500 600 700 800 900
The multitable insert statement looks like this:
INSERT ALL INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date, sales_sun) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+1, sales_mon) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+2, sales_tue) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+3, sales_wed) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+4, sales_thu) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+5, sales_fri) INTO sales (prod_id, cust_id, time_id, amount) VALUES (product_id, customer_id, weekly_start_date+6, sales_sat) SELECT product_id, customer_id, weekly_start_date, sales_sun, sales_mon, sales_tue, sales_wed, sales_thu, sales_fri, sales_sat FROM sales_input_table;
Assuming these are the only rows in the
sales table, the contents now look like this:
SELECT * FROM sales; PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD AMOUNT COST ---------- ---------- --------- - ---------- ------------- ---------- ---------- 111 222 01-OCT-00 100 111 222 02-OCT-00 200 111 222 03-OCT-00 300 111 222 04-OCT-00 400 111 222 05-OCT-00 500 111 222 06-OCT-00 600 111 222 07-OCT-00 700 222 333 08-OCT-00 200 222 333 09-OCT-00 300 222 333 10-OCT-00 400 222 333 11-OCT-00 500 222 333 12-OCT-00 600 222 333 13-OCT-00 700 222 333 14-OCT-00 800 333 444 15-OCT-00 300 333 444 16-OCT-00 400 333 444 17-OCT-00 500 333 444 18-OCT-00 600 333 444 19-OCT-00 700 333 444 20-OCT-00 800 333 444 21-OCT-00 900