|Oracle® Database SQL Reference
10g Release 1 (10.1)
Part Number B10759-01
INSERT statement to add rows to a table, the base table of a view, a partition of a partitioned table or a subpartition of a composite-partitioned table, or an object table or the base table of an object view.
For you to insert rows into a table, the table must be in your own schema or you must have the
INSERT object 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 the
INSERT object privilege on the base table. Also, if the view is in a schema other than your own, then you must have the
INSERT object privilege on the view.
If you have the
TABLE system privilege, then you can also insert rows into any table or the base table of any view.
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 Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path
INSERT, the database 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 the Oracle direct-path loader utility, SQL*Loader.
INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional
INSERT serially without returning any message, unless otherwise noted:
You can have multiple direct-path
INSERT statements 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.
Queries that access the same table, partition, or index are allowed before the direct-path
INSERT statement, but not after it.
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path
INSERT in the same transaction, then the database returns an error and rejects the statement.
The target table cannot be index organized or part of a cluster.
The target table cannot contain object type or LOB columns.
The target table cannot have any triggers or referential integrity constraints defined on it.
The target table cannot be replicated.
A transaction containing a direct-path
INSERT statement 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, then 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, then 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.
You can use the
subquery to insert past data into
table. Please refer to the flashback_query_clause of
SELECT for more information on this clause.
If you retrieve values through a subquery, then 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.
See Also:"Inserting Values into Tables: Examples"
INTO clause to specify the target object or objects into which the database is to insert data.
DML_table_expression_clause to specify the objects into which data is being inserted.
WITH READ ONLY
If you specify an index, index partition, or index subpartition that has been marked
UNUSABLE, then the
INSERT statement will fail unless the
SKIP_UNUSABLE_INDEXES session parameter has been set to
TRUE. Please refer to ALTER SESSION for information on the
SKIP_UNUSABLE_INDEXES session parameter.
Specify the schema containing the table, view, or materialized view. If you omit
schema, then the database 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, then the database inserts rows into the base table of the view.
You cannot insert rows into a read-only materialized view. If you insert rows into a writable materialized view, then the database inserts the rows into the underlying container table. However, the insertions are overwritten at the next refresh operation.If you insert rows into an updatable materialized view that is part of a materialized view group, then the database also inserts the corresponding rows into the master 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.
table, or the base table of
view, contains one or more domain index columns, then this statement executes the appropriate indextype insert routine.
INSERT statement against a table fires any
INSERT triggers defined on the table.
See Also:Oracle 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, then the database returns an error.
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 Database distributed functionality.
If you omit
dblink, then Oracle Database assumes that the table or view is on the local database.
subquery_restriction_clause to restrict the subquery in one of the following ways:
WITH READ ONLY to indicate that the table or view cannot be updated.
WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the
Specify the name of the
CHECK OPTION constraint. If you omit this identifier, then Oracle automatically assigns the constraint a name of the form
n, where n is an integer that makes the constraint name unique within the database.
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 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.
Note:In earlier releases of Oracle, when
See Also:"Table Collections: Examples"
Specify a correlation name, which is an alias for the table, view, materialized view, or subquery to be referenced elsewhere in the statement.
You cannot specify
t_alias during a multitable insert.
Specify a column of the table, view, or materialized 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 or more of the table's columns from this list, then the column value of that column for the inserted row is the column default value as specified when the table was created or last altered. If any omitted column has a
NULL constraint and no default value, then the database returns an error indicating that the constraint has been violated and rolls back the
INSERT statement. Please refer to CREATE TABLE for more information on default column values.
If you omit the column list altogether, then the
values_clause or query must specify values for all columns in the table.
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, then 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, then 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 the database 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, then that value is inserted. If no default value for the corresponding column has been specified, then the database inserts null. Please refer to "About SQL Expressions " and SELECT for syntax of valid expressions.
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 name and filename.
When inserting into a list-partitioned table, you cannot insert a value into the partitioning key column that does not already exist in the
partition_value list of one of the partitions.
You cannot specify
DEFAULT when inserting into a view.
If you insert string literals into a
RAW column, then during subsequent queries Oracle Database will perform a full table scan rather than using any index that might exist on the
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.
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
The following restrictions apply to the
expr is restricted as follows:
expr must be a simple expression or a single-set aggregate function expression. You cannot combine simple expressions and single-set aggregate function expressions in the same
Single-set aggregate function expressions cannot include the
You cannot specify the
returning_clause for a multitable insert.
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
OF trigger has been defined.
See Also:PL/SQL User's Guide and Reference for information on using the
In a multitable insert, you insert computed rows derived from the rows returned from the evaluation of a subquery into one or more tables.
Table aliases are not defined by the select list of the subquery. Therefore, they are not visible in the clauses dependent on the select list. For example, this can happen when trying to refer to an object column in an expression. To use an expression with a table alias, you must put the expression into the select list with a column alias, and then refer to the column alias in the
VALUES clause or
WHEN condition of the multitable insert
ALL followed by multiple
insert_into_clauses to perform an unconditional multitable insert. Oracle Database executes each
insert_into_clause once for each row returned by the subquery.
conditional_insert_clause to perform a conditional multitable insert. Oracle Database filters each
insert_into_clause through the corresponding
WHEN condition, which determines whether that
insert_into_clause is executed. Each expression in the
WHEN condition must refer to columns returned by the select list of the subquery. A single multitable insert statement can contain up to 127
If you specify
ALL, then the database 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, the database executes the corresponding
INTO clause list.
If you specify
FIRST, then the database evaluates each
WHEN clause in the order in which it appears in the statement. For the first
WHEN clause that evaluates to true, the database 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, then:
If you have specified an
ELSE clause, then the database executes the
INTO clause list associated with the
If you did not specify an else clause, then the database takes no action for that row.
See Also:"Multitable Inserts: Examples "
You can perform multitable inserts only on tables, not on views or materialized views.
You cannot perform a multitable insert into a remote table.
You cannot specify a table collection expression when performing a multitable insert.
In a multitable insert, all of the
insert_into_clauses cannot combine to specify more than 999 target columns.
Multitable inserts are not parallelized in a Real Application Clusters environment, or if any target table is index organized, or if any target table has a bitmap index defined on it.
Plan stability is not supported for multitable insert statements.
The subquery of the multitable insert statement cannot use a sequence.
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, then the database 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.
subquery returns the partial or total equivalent of a materialized view, then the database may use the materialized view for query rewrite in place of one or more tables specified in
See Also:Oracle Data Warehousing Guide for more information on materialized views and query rewrite
subquery refers to remote objects, then 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_table_expression_clause refers to any remote objects, then the
INSERT operation will run serially without notification. See parallel_clause for more information
The following statement inserts a row into the sample table
INSERT INTO departments VALUES (280, 'Recreation', 121, 1700);
departments table had been created with a default value of 121 for the
manager_id column, then you could issue the same statement as follows:
INSERT INTO departments VALUES (280, 'Recreation', DEFAULT, 1700);
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
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 was created in "Merging into a Table: 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
employees table owned by the user
hr on the database accessible by the database link
INSERT INTO employees@remote VALUES (8002, 'Juan', 'Fernandez', 'email@example.com', NULL, TO_DATE('04-OCT-1992', 'DD-MON-YYYY'), 'SH_CLERK', 3000, NULL, 121, 20);
The following statement inserts a new row containing the next value of the
departments_seq sequence into the
INSERT INTO departments VALUES (departments_seq.nextval, 'Entertainment', 162, 1400);
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', 'firstname.lastname@example.org', SYSDATE, 'SH_CLERK', 2400) RETURNING salary*12, job_id INTO :bnd1, :bnd2;
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
employee_t subtype of
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));
The following example copies
LONG data to a LOB column in the following
CREATE TABLE long_tab (pic_id NUMBER, long_pics LONG RAW);
First you must create a table with a LOB.
CREATE TABLE lob_tab (pic_id NUMBER, lob_pics BLOB);
Next, use an
SELECT statement to copy the data in all rows for the
LONG column into the newly created LOB column:
INSERT INTO lob_tab SELECT pic_id, 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, then you can simply drop the
LONG column from the table as follows:
ALTER TABLE long_tab DROP COLUMN long_pics;
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.
Note:A number of constraints on the sales table have been disabled for purposes of this example, because the example ignores a number of table columns for the sake of brevity.
The input table looks like this:
SELECT * FROM sales_input_table; 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
The next examples insert into multiple tables. Suppose you want to provide to sales representatives some information on orders of various sizes. The following example creates tables for small, medium, large, and special orders and populates those tables with data from the sample table
CREATE TABLE small_orders (order_id NUMBER(12) NOT NULL, customer_id NUMBER(6) NOT NULL, order_total NUMBER(8,2), sales_rep_id NUMBER(6) ); CREATE TABLE medium_orders AS SELECT * FROM small_orders; CREATE TABLE large_orders AS SELECT * FROM small_orders; CREATE TABLE special_orders (order_id NUMBER(12) NOT NULL, customer_id NUMBER(6) NOT NULL, order_total NUMBER(8,2), sales_rep_id NUMBER(6), credit_limit NUMBER(9,2), cust_email VARCHAR2(30) );
The first multitable insert populates only the tables for small, medium, and large orders:
INSERT ALL WHEN order_total < 1000000 THEN INTO small_orders WHEN order_total > 1000000 AND order_total < 2000000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
You can accomplish the same thing using the
ELSE clause in place of the insert into the
INSERT ALL WHEN order_total < 100000 THEN INTO small_orders WHEN order_total > 100000 AND order_total < 200000 THEN INTO medium_orders ELSE INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;
The next example inserts into the small, medium, and large tables, as in the preceding example, and also puts orders greater than 2,900,000 into the
special_orders table. This table also shows how to use column aliases to simplify the statement:
INSERT ALL WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 200000 THEN into large_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id;
Finally, the next example uses the
FIRST clause to put orders greater than 2,900,000 into the
special_orders table and exclude those orders from the
INSERT FIRST WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders WHEN ottl > 200000 THEN INTO large_orders VALUES(oid, ottl, sid, cid) SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id;