Oracle9i SQL Reference Release 2 (9.2) Part Number A96540-02 |
|
|
View PDF |
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.
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, then you must have INSERT
privilege on the view.
If you have the INSERT
ANY
TABLE
system privilege, then 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.
Direct-path INSERT
is subject to a number of restrictions. If any of these restrictions is violated, then Oracle executes conventional INSERT
serially without returning any message (unless otherwise noted):
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.INSERT
statement, but not after it.INSERT
in the same transaction, then Oracle returns an error and rejects the statement.ROW_LOCKING
initialization parameter cannot be set to INTENT
.INSERT
statement cannot be or become distributed.
See Also:
|
insert::=
DML_table_expression_clause::=
(subquery::=
--part of SELECT
syntax, subquery_restriction_clause::=
, table_collection_expression::=
)
subquery_restriction_clause::=
table_collection_expression::=
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 PARALLEL
specified.
See Also:
|
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 flashback_clause
in subquery
to insert past data into table
.
See Also:
the |
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.
Use the INSERT
INTO
clause to specify the target object or objects into which Oracle is to insert data.
Use the INTO
dml_table_expression_clause
to specify the objects into which data is being inserted.
table
(or the base table of view
) contains any domain indexes marked IN_PROGRESS
or FAILED
.UNUSABLE
.ORDER
BY
clause 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.WITH
CHECK
OPTION
, then you can insert into the view only rows that satisfy the view's defining query.returning_clause
.INSTEAD
OF
triggers if the view's defining query contains one of the following constructs:
DISTINCT
operatorGROUP
BY
, ORDER
BY
, CONNECT
BY
, or START
WITH
clauseSELECT
listSELECT
listUNUSABLE
, then the INSERT
statement will fail unless the SKIP_UNUSABLE_INDEXES
session parameter has been set to TRUE
.
See Also:
ALTER SESSION for information on the |
Specify the schema containing the table, view, or materialized view. If you omit schema
, then 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, then 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, then 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:
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, then then Oracle 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's distributed functionality.
If you omit dblink
, then 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 and "Inserting into a Remote Database: Example" |
Use the subquery_restriction_clause
to restrict the subquery in one of the following ways:
Specify WITH
READ
ONLY
to indicate that the table or view cannot be updated.
Specify WITH
CHECK
OPTION
to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.
Specify the name of the CHECK
OPTION
constraint. If you omit this identifier, Oracle automatically assigns the constraint a name of the form SYS_C
n
, where n
is an integer that makes the constraint name unique within the database.
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 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 |
Specify a correlation name (alias) for the table, 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 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 or more of the table's columns from this list, then 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 omitted column has a NOT
NULL
constraint and no default value, 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, then 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 |
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 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, then that value is inserted. If no default value for the corresponding column has been specified, then Oracle inserts null.
Note: Parallel direct-path |
BFILE
value until you have initialized the BFILE
locator to null or to a directory alias and filename.partition_value
list of one of the partitions.DEFAULT
when inserting into a view.
Note: If you insert string literals into a |
See Also:
|
The returning clause retrieves the rows affected by a DML (INSERT
, UPDATE
, or 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.
The INTO
clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item
list.
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.
You cannot:
returning_clause
for a multitable insert.LONG
types with this clause.INSTEAD
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.
Specify 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. 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 WHEN
clauses.
If you specify ALL
, then 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
, then 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:
ELSE
clause, then Oracle executes the INTO
clause list associated with the ELSE
clause.insert_into_clause
s cannot combine to specify more than 999 target columns.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 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 LONG
s to LOBs in a view, you must perform the migration on the base table, and then add the LOB to the view.
subquery
returns (in part or totally) the equivalent of an existing materialized view, then Oracle may use the materialized view (for query rewrite) in place of one or more tables specified in subquery
.
See Also:
Oracle9i 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
(in CREATE TABLE) for more information
See Also:
|
The following statement inserts a row into the sample table departments
:
INSERT INTO departments VALUES (280, 'Recreation', 121, 1700);
If the 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', 'pgregory@oracle.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', 'pgregory@oracle.com', 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 "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 remote
:
INSERT INTO employees@remote VALUES (8002, 'Juan', 'Fernandez', 'juanf@hr.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 sequence into the departments
table:
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', 'john.doe@oracle.com', 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 person_t
, and the third insert uses the part_time_emp_t
subtype of employee_t
:
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 TREAT and SYS_TYPEID, and "IS OF type Conditions".
The following example copies LONG
data to a LOB column in the following long_tab
table:
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 INSERT
... 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 inserts a row into the sample table pm.print_media
. The example uses the BFILENAME
function to identify a binary file on the server's file system:
CREATE DIRECTORY media_dir AS '/demo/schema/product_media'; INSERT INTO print_media (product_id, ad_id, ad_graphic) VALUES (3000, 31001, bfilename('MEDIA_DIR', 'modem_comp_ad.gif'));
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" (very large) orders and populates those tables with data from the sample table oe.orders
:
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 large_orders
table:
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 large_orders
table:
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;