Use the INSERT statement to include rows to a table, the base table that a view, a partition that a partitioned table or a subpartition the a composite-partitioned table, or an item table or the basic table of an item view.

You are watching: You cannot insert a row containing a null attribute value using sql.

Additional Topics

Prerequisites

For you come insert rows right into a table, the table should be in your own schema or you must have the INSERT thing privilege top top the table.

For you to insert rows into the base table of a view, the owner of the schema comprise the see must have the INSERT object privilege on the base table. Also, if the see is in a schema other than your own, then you must have actually the INSERT object privilege top top the view.

If you have actually the INSERT any kind of TABLE device privilege, then you can likewise insert rows into any table or the basic table of any type of view.

Conventional and Direct-Path INSERT

You deserve to use the INSERT statement to insert data into a table, partition, or view in two ways: standard INSERT and also direct-path INSERT. When you worry a typical INSERT statement, ptcouncil.net Database reuses free space in the table into which you room inserting and also maintains referential truth constraints. V direct-path INSERT, the database appends the placed data ~ existing data in the table. Data is written straight into data files, bypassing the buffer cache. Cost-free space in the currently data is no reused. This alternative enhances performance during insert operations and is comparable to the use of the ptcouncil.net direct-path loader utility, SQL*Loader. Once you insert right into a table that has actually been produced in parallel mode, direct-path INSERT is the default.

The path in i m sorry the database generates redo and undo data relies in component on even if it is you are using standard or direct-path INSERT:

Conventional INSERT constantly generates maximal redo and also undo for alters to both data and also metadata, nevertheless of the logging setup of the table and also the archivelog and force logging setups of the database.

Direct-path INSERT generates both redo and also undo because that metadata changes, because these are essential for operation recovery. Because that data changes, undo and redo are created as follows:

Direct-path INSERT constantly bypasses cancel generation because that data changes.

If the database is no in ARCHIVELOG or pressure LOGGING mode, then no redo is created for data changes, nevertheless of the logging setting of the table.

If the database is in ARCHIVELOG mode (but not in force LOGGING mode), then direct-path INSERT generates data redo for LOGGING tables but not because that NOLOGGING tables.

If the database is in ARCHIVELOG and FORCE LOGGING mode, climate direct-path SQL create data redo because that both LOGGING and NOLOGGING tables.

Direct-path INSERT is subject to a variety of restrictions. If any of these constraints is violated, climate ptcouncil.net Database executes conventional INSERT serially without returning any kind of message, uneven otherwise noted:

You deserve to have multiple direct-path INSERT explanation in a single transaction, v or without various other DML statements. However, ~ one DML statement alters a certain table, partition, or index, no various other DML declare in the transaction can accessibility that table, partition, or index.

Queries that accessibility the exact same table, partition, or index are allowed before the direct-path INSERT statement, yet not after it.

If any type of serial or parallel statement attempts to accessibility a table the has currently been amendment by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.

The target table can not be the a cluster.

The target table can not contain object kind columns.

Direct-path INSERT is not sustained for an index-organized table (IOT) if the is not partitioned, if it has actually a mapping table, or if that is recommendation by a shown up view.

Direct-path INSERT right into a solitary partition of one index-organized table (IOT), or right into a partitioned IOT with just one partition, will be excellent serially, even if the IOT was developed in parallel setting or girlfriend specify the APPEND or APPEND_VALUES hint. However, direct-path INSERT operations right into a partitioned IOT will certainly honor parallel setting as lengthy as the partition-extended name is not used and the IOT has more than one partition.

The target table can not have any type of triggers or referential verity constraints characterized on it.

The target table cannot be replicated.

A transaction include a direct-path INSERT statement cannot be or end up being distributed.

You cannot query or change direct-path inserted data immediately after the insert is complete. If you effort to do so, one ORA-12838 error is generated. Girlfriend must very first issue a walk statement before attempting to review or modify the newly-inserted data.


Syntax

insert::=

*
Description of the illustration insert.gif

(single_table_insert ::=, multi_table_insert ::=)

single_table_insert ::=

*
Description the the illustration single_table_insert.gif

(insert_into_clause ::=, values_clause ::=, returning_clause::=, subquery::=, error_logging_clause ::=)

insert_into_clause ::=

*
Description that the illustration insert_into_clause.gif

(DML_table_expression_clause::=)

values_clause ::=

*
Description of the illustration values_clause.gif

returning_clause::=

*
Description of the illustration returning_clause.gif

multi_table_insert ::=

*
Description that the illustration multi_table_insert.gif

(insert_into_clause ::=, values_clause ::=, conditional_insert_clause ::=, subquery::=, error_logging_clause ::=)

conditional_insert_clause ::=

*
Description of the illustration conditional_insert_clause.gif

(insert_into_clause ::=, values_clause ::=)

DML_table_expression_clause::=

*
Description of the illustration dml_table_expression_clause.gif

(partition_extension_clause::=, subquery::=—part of SELECT, subquery_restriction_clause::=, table_collection_expression ::=)

partition_extension_clause::=

*
Description that the illustration partition_extension_clause.gif

subquery_restriction_clause::=

*
Description of the illustration subquery_restriction_clause.gif

table_collection_expression ::=

*
Description the the illustration table_collection_expression.gif

error_logging_clause ::=

*
Description of the illustration error_logging_clause.gif

Semantics

hint

Specify a comment the passes instructions to the optimizer on picking an execution setup for the statement.

For a multitable insert, if girlfriend specify the PARALLEL hint for any target table, climate the entire multitable insert statement is parallelized also if the target tables have actually not been created or transformed with PARALLEL specified. If you perform not specify the PARALLEL hint, climate the insert operation will not be parallelized uneven all target tables were developed or transformed with PARALLEL specified.


single_table_insert

In a single-table insert, you insert values into one row of a table, view, or manifested view by specifying values explicitly or by retrieving the values through a subquery.

You deserve to use the flashback_query_clause in subquery to insert past data right into table. Refer to the flashback_query_clause of select for much more information top top this clause.

Restriction on Single-table InsertsIf you retrieve values v a subquery, then the select list of the subquery must have actually the same variety of columns as the column list that the INSERT statement. If you omit the pillar list, then the subquery must administer values because that every obelisk in the table.


insert_into_clause

Use the INSERT right into clause come specify the target object or objects right into which the database is come insert data.

DML_table_expression_clause

Use the right into DML_table_expression_clause to specify the objects into which data is gift inserted.

schemaSpecify the schema include the table, view, or shown up view. If you omit schema, climate the database presume the thing is in your very own schema.

table | view | materialized_view | subquerySpecify the name of the table or thing table, check out or object view, manifested view, or the pillar or columns returned by a subquery, right into which rows are to it is in inserted. If friend specify a watch or thing view, then the database inserts rows into the basic table the the view.

You cannot insert rows right into a read-only shown up view. If friend insert rows right into a writable manifested view, climate the database inserts the rows into the basic container table. However, the insertions room overwritten at the next refresh operation. If you insert rows into an updatable manifested view the is component of a manifested view group, then the database also inserts the matching rows right into the master table.

If any kind of value to be put is a REF to an item table, and also if the thing table has a primary vital object identifier, then the column right into which friend insert the REF should be a REF shaft with a referential truth or scope constraint to the thing table.

If table, or the base table of view, includes one or an ext domain index columns, then this declare executes the suitable indextype insert routine.

Issuing one INSERT statement versus a table fires any INSERT triggers characterized on the table.


Restrictions ~ above the DML_table_expression_clauseThis clause is subject to the complying with restrictions:

You cannot execute this explain if table or the basic table of check out contains any kind of domain indexes marked IN_PROGRESS or FAILED.

You cannot insert right into a partition if any affected index partitions are marked UNUSABLE.

With regard to the order BY i of the subquery in the DML_table_expression_clause, notified is guaranteed just for the rows being inserted, and also only in ~ each extent of the table. Bespeak of new rows through respect to existing rows is not guaranteed.

If a view was created using the WITH check OPTION, then you have the right to insert right into the view only rows that satisfy the specifying query that the view.

If a see was produced using a solitary base table, climate you deserve to insert rows into the view and also then retrieve those values using the returning_clause.

partition_extension_clause clues the name or partition crucial value the the partition or subpartition within table, or the base table that view, targeted because that inserts.

If a heat to be placed does not map right into a mentioned partition or subpartition, climate the database return an error.

Restriction ~ above Target Partitions and also SubpartitionsThis clause is not valid because that object tables or object views.


dblink clues a finish or partial surname of a database attach to a far database where the table or check out is located. You can insert rows into a remote table or view just if you room using ptcouncil.net Database distributed functionality.

If friend omit dblink, climate ptcouncil.net Database assumes the the table or check out is on the local database.


subquery_restriction_clauseUse the subquery_restriction_clause come restrict the subquery in among the following ways:

WITH read ONLYSpecify through READ only to suggest that the table or check out cannot it is in updated.

WITH check OPTIONSpecify WITH examine OPTION to show that ptcouncil.net Database prohibits any kind of changes come the table or watch that would develop rows that room not contained in the subquery. When provided in the subquery that a DML statement, you have the right to specify this i in a subquery in the indigenous clause but not in subquery in the wherein clause.

CONSTRAINT constraintSpecify the name of the inspect OPTION constraint. If you omit this identifier, then ptcouncil.net immediately assigns the constraint a surname of the type SYS_Cn, wherein n is an integer that renders the constraint name distinctive within the database.


table_collection_expression

The table_collection_expression lets you inform ptcouncil.net that the worth of collection_expression must be treated together a table for purposes of query and also DML operations. The collection_expression can be a subquery, a column, a function, or a arsenal constructor. Regardless of that form, it have to return a repertoire value—that is, a worth whose type is nested table or varray. This procedure of extract the aspects of a collection is called collection unnesting.

The optional plus (+) is appropriate if you space joining the TABLE collection expression through the parent table. The + create an outer sign up with of the two, so that the query return rows indigenous the external table also if the collection expression is null.


Note:

In earlier releases of ptcouncil.net, as soon as collection_expression was a subquery, table_collection_expression to be expressed as THE subquery. That usage is currently deprecated.

t_alias

Specify a correlation name, i beg your pardon is one alias for the table, view, shown up view, or subquery to be referenced somewhere else in the statement.

Restriction on Table AliasesYou cannot specify t_alias throughout a multitable insert.

column

Specify a obelisk of the table, view, or manifested view. In the inserted row, each column in this perform is assigned a worth from the values_clause or the subquery.

If friend omit one or an ext of the table"s columns from this list, then the column value that that tower for the put row is the tower default worth as specified when the table was created or last altered. If any type of omitted column has actually a not NULL constraint and no default value, climate the database returns an error indicating that the constraint has been violated and rolls earlier the INSERT statement. To express to develop TABLE for much more information ~ above default obelisk values.

If girlfriend omit the column list altogether, then the values_clause or query need to specify worths for every columns in the table.

values_clause

For a single-table insert operation, specify a heat of worths to be inserted into the table or view. You should specify a value in the values_clause because that each column in the column list. If girlfriend omit the column list, climate the values_clause must provide values for every pillar in the table.

For a multitable insert operation, every expression in the values_clause need to refer to columns changed by the select list of the subquery. If you omit the values_clause, climate the pick list that the subquery determines the worths to be inserted, so it must have actually the same variety of columns together the shaft list that the corresponding insert_into_clause. If you carry out not point out a shaft list in the insert_into_clause, then the computed heat must administer values for every columns in the target table.

For both species of insert operations, if girlfriend specify a pillar list in the insert_into_clause, then the database assigns come each obelisk in the list a matching value native 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 equivalent column the the table or view, then that worth is inserted. If no default worth for the matching column has been specified, climate the database inserts null. Refer to "About SQL Expressions" and also SELECT because that syntax of precious expressions.

Restrictions on placed ValuesThe value room subject to the adhering to restrictions:

You can not insert a BFILE value till you have actually initialized the BFILE locator come null or come a magazine name and also filename.


When inserting right into a list-partitioned table, you can not insert a value into the partitioning crucial column the does not currently exist in the partition_key_value list of among the partitions.

You cannot specify DEFAULT when inserting into a view.

If you insert wire literals right into a life column, then during subsequent queries ptcouncil.net Database will perform a full table scan quite than using any type of index that can exist on the raw column.


returning_clause

The returning clause retrieves the rows impacted by a DML statement. You have the right to specify this i for tables and also materialized views and also for views v a single base table.

When operation on a single row, a DML statement v a returning_clause can retrieve pillar expressions making use of the affected row, rowid, and also REFs to the impacted row and also store castle in organize variables or PL/SQL variables.

When operating on multiple rows, a DML statement with the returning_clause stores values from expressions, rowids, and REFs entailing the impacted rows in tie arrays.

exprEach article in the expr list have to be a precious expression syntax.

INTOThe into clause suggests that the values of the readjusted rows room to it is in stored in the variable(s) specified in data_item list.

data_itemEach data_item is a host variable or PL/SQL variable the stores the recall expr value.

For each expression in the returning list, you need to specify a corresponding type-compatible PL/SQL variable or organize variable in the into list.

RestrictionsThe complying with restrictions use to the return clause:

The expr is limited as follows:

For UPDATE and DELETE statements every expr have to be a straightforward expression or a single-set aggregate role expression. You cannot combine an easy expressions and also single-set aggregate role expressions in the very same returning_clause. For INSERT statements, every expr should be a straightforward expression. Aggregate functions space not sustained in one INSERT statement return clause.

Single-set aggregate function expressions cannot incorporate the distinctive keyword.

If the expr list includes a primary vital column or other NOT NULL column, climate the upgrade statement falls short if the table has a prior to UPDATE create defined ~ above it.

You cannot specify the returning_clause because that a multitable insert.

You cannot usage this clause with parallel DML or through remote objects.

You can not retrieve LONG species with this clause.

You can not specify this clause for a watch on i beg your pardon an instead OF trigger has to be defined.


See Also:

ptcouncil.net Database PL/SQL Language recommendation for details on utilizing the mass COLLECT i to return multiple values to repertoire variables

multi_table_insert

In a multitable insert, girlfriend insert computed rows derived from the rows returned from the testimonial of a subquery into one or an ext tables.

Table aliases are not characterized by the pick list of the subquery. Therefore, they room not visible in the clauses dependent on the select list. For example, this can take place when trying come refer to an item column in one expression. To usage an expression v a table alias, you have to put the expression right into the choose list v a column alias, and also then refer to the pillar alias in the values clause or WHEN problem of the multitable insert.

ALL into_clause

Specify ALL followed by many insert_into_clauses to perform an unconditional multitable insert. ptcouncil.net Database executes every insert_into_clause as soon as for each row went back by the subquery.

conditional_insert_clause

Specify the conditional_insert_clause to carry out a conditional multitable insert. ptcouncil.net Database filters every insert_into_clause through the matching WHEN condition, which determines whether the insert_into_clause is executed. Each expression in the WHEN problem must describe columns returned by the pick list of the subquery. A single multitable insert statement have the right to contain as much as 127 once clauses.

ALLIf friend specify ALL, the default value, then the database evaluate each when clause nevertheless of the results of the testimonial of any kind of other when clause. Because that each when clause whose condition evaluates come true, the database executes the corresponding INTO i list.

FIRSTIf you specify FIRST, climate the database evaluate each as soon as clause in the stimulate in i beg your pardon it shows up in the statement. Because that the an initial WHEN clause that evaluate to true, the database executes the matching INTO clause and skips subsequent once clauses because that the given row.

ELSE clauseFor a offered row, if no as soon as clause evaluate to true, then:

If you have specified an else clause, then the database executes the into clause list associated with the rather clause.

If friend did no specify an else clause, then the database bring away no action for that row.


Restrictions top top Multitable InsertsMultitable inserts room subject to the following restrictions:

You can perform multitable inserts just on tables, no on views or manifested views.

You cannot perform a multitable insert into a far table.

You can not specify a TABLE collection expression once performing a multitable insert.

In a multitable insert, every one of the insert_into_clauses cannot integrate to specify more than 999 target columns.

Multitable inserts space not parallelized if any type of target table is index organized or if any kind of target table has actually a bitmap index defined on it.

Plan security is not sustained for multitable insert statements.

You cannot specify a sequence in any component of a multitable insert statement. A multitable insert is taken into consideration a single SQL statement. Therefore, the an initial reference come NEXTVAL generates the following number, and also all subsequent references in the explain return the exact same number.

subquery

Specify a subquery the returns rows that are put into the table. The subquery have the right to refer to any kind of table, view, or materialized view, consisting of the target tables that the INSERT statement. If the subquery selects no rows, climate the database inserts no rows into the table.

You can use subquery in combination with the TO_LOB role to convert the values in a long column come LOB values in one more column in the exact same or an additional table.

To migrate long values to LOB values in another column in a view, you need to perform the migrate on the basic table and then add the LOB shaft to the view.

To migrate long values on a far table to LOB worths in a local table, you must perform the migrate on the remote table using the TO_LOB function, and then perform an INSERT ... Subquery operation to copy the LOB values from the remote table right into the regional table.

Notes ~ above Inserting v a SubqueryThe complying with notes apply when inserting v a subquery:

If subquery refers to remote objects, climate the INSERT operation can run in parallel as long as the referral does no loop ago to an item on the regional database. However, if the subquery in the DML_table_expression_clause advert to any remote objects, then the INSERT procedure will operation serially there is no notification. Check out parallel_clause for much more information.


error_logging_clause

The error_logging_clause lets you catch DML errors and the log shaft values that the affected rows and save castle in one error logging table.

INTO tableSpecify the surname of the error logging table. If you omit this clause, climate the database assigns the default name generated by the DBMS_ERRLOG package. The default error log in table name is ERR$_ complied with by the very first 25 personalities of the surname of the table top top which the DML procedure is being executed.

simple_expressionSpecify the value to be used as a declare tag, so that you have the right to identify the errors native this declare in the error logging table. The expression have the right to be either a text literal, a number literal, or a basic SQL expression such as a bind variable. Friend can also use a role expression if you convert it to a text literal — because that example, TO_CHAR(SYSDATE).

REJECT LIMITThis clause lets you specify an integer together an upper limit because that the number of errors to be logged prior to the explain terminates and rolls ago any changes made by the statement. The default rejection limit is zero. For parallel DML operations, the disapprove limit is used to every parallel server.

Restrictions on DML Error Logging

The following conditions cause the statement to fail and roll ago without invoking the error logging capability:

Violated deferred constraints.

Any direct-path INSERT or MERGE procedure that raises a distinct constraint or table of contents violation.

Any update operation UPDATE or MERGE the raises a unique constraint or index violation.

You cannot monitor errors in the error logging table for LONG, LOB, or object form columns. However, the table the is the target of the DML operation can contain these species of columns.

If you develop or change the matching error logging table so that it consists of a shaft of an unsupported type, and if the surname of that column corresponds to an unsupported column in the target DML table, climate the DML statement stops working at parse time.

If the error logging table does no contain any unsupported column types, then every DML errors space logged till the disapprove limit that errors is reached. Because that rows on i m sorry errors occur, column values with corresponding columns in the error logging table room logged along with the control information.


Examples

Inserting Values into Tables: ExamplesThe adhering to statement inserts a row right into the sample table departments:

INSERT into departments worths (280, "Recreation", 121, 1700);If the departments table had actually been created with a default value of 121 because that the manager_id column, then you could concern the same statement together follows:

INSERT into departments values (280, "Recreation", DEFAULT, 1700);The following statement inserts a heat with six columns right into the employee table. Among these columns is assigned NULL and also another is assigned a number in clinical notation:

INSERT into employees (employee_id, last_name, email, hire_date, job_id, salary, commission_pct) values (207, "Gregory", "pgregory
example.com", sysdate, "PU_CLERK", 1.2E3, NULL);The adhering to statement has the same impact as the coming before example, however uses a subquery in the DML_table_expression_clause:

INSERT into (SELECT employee_id, last_name, email, hire_date, job_id, salary, commission_pct native employees) values (207, "Gregory", "pgregory
example.com", sysdate, "PU_CLERK", 1.2E3, NULL);Inserting Values v a Subquery: ExampleThe adhering to statement copies employees whose commission above 25% of your salary into the bonuses table, i m sorry was developed in "Merging right into a Table: Example":

INSERT into bonuses choose employee_id, salary*1.1 indigenous employees wherein commission_pct > 0.25; Inserting into a Table with Error Logging: ExampleThe complying with statements create a raises table in the sample schema hr, develop an error logging table utilizing the DBMS_ERRLOG package, and populate the raises table with data from the employees table. Among the inserts violates the inspect constraint top top raises, and that row have the right to be checked out in errlog. If much more than ten errors had occurred, then the explain would have aborted, rolling ago any insertions made:

CREATE TABLE raises (emp_id NUMBER, sal NUMBER CONSTRAINT check_sal CHECK(sal > 8000));EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG("raises", "errlog");INSERT into raises choose employee_id, salary*1.1 from employees where commission_pct > .2 log in ERRORS into errlog ("my_bad") disapprove LIMIT 10;SELECT ORA_ERR_MESG$, ORA_ERR_TAG$, emp_id, sal from errlog;ORA_ERR_MESG$ ORA_ERR_TAG$ EMP_ID SAL--------------------------- -------------------- ------ -------ORA-02290: inspect constraint my_bad 161 7700 (HR.SYS_C004266) violatedInserting right into a remote Database: ExampleThe adhering to statement inserts a row right into the employees table owned by the user hr on the database accessible by the database connect remote:

INSERT right into employees
example.com", NULL, TO_DATE("04-OCT-1992", "DD-MON-YYYY"), "SH_CLERK", 3000, NULL, 121, 20); Inserting sequence Values: ExampleThe following statement inserts a brand-new row include the next value that the departments_seq sequence into the departments table:

INSERT into departments values (departments_seq.nextval, "Entertainment", 162, 1400); Inserting using Bind Variables: ExampleThe following instance returns the worths of the put rows right into output tie variables :bnd1 and also :bnd2. The bind variables must very first be declared.

INSERT into employees (employee_id, last_name, email, hire_date, job_id, salary) worths (employees_seq.nextval, "Doe", "john.doe
example.com", SYSDATE, "SH_CLERK", 2400) returning salary*12, job_id right into :bnd1, :bnd2;Inserting right into a Substitutable Tables and Columns: ExamplesThe following instance inserts right into the people table, which is produced in "Substitutable Table and also Column Examples". The very first statement offers the root kind person_t. The second insert uses the employee_t subtype the person_t, and the third insert uses the part_time_emp_t subtype the employee_t:

INSERT into persons worths (person_t("Bob", 1234));INSERT right into persons values (employee_t("Joe", 32456, 12, 100000));INSERT right into persons values ( part_time_emp_t("Tim", 5678, 13, 1000, 20));The following example inserts right into the publications table, i beg your pardon was produced in "Substitutable Table and Column Examples". Notice that specification that the attribute values is the same 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 publications VALUES ( "Mixing School and also Work", part_time_emp_t("Tim", 5678, 13, 1000, 20));You deserve to extract data native substitutable tables and also columns using integrated functions and conditions. Because that examples, see the features TREAT and also SYS_TYPEID, and also "IS OF form Condition".

Inserting making use of the TO_LOB Function: ExampleThe adhering to example duplicates LONG data to a LOB shaft in the following long_tab table:

CREATE TABLE long_tab (pic_id NUMBER, long_pics long RAW);First friend must develop a table v a LOB.

CREATE TABLE lob_tab (pic_id NUMBER, lob_pics BLOB);Next, usage an INSERT ... Choose statement to copy the data in all rows because that the long column into the newly created LOB column:

INSERT right into lob_tab pick pic_id, TO_LOB(long_pics) from long_tab;When you are confident that the migration has actually been successful, you deserve to drop the long_pics table. Alternatively, if the table includes other columns, climate you have the right to simply autumn the long column indigenous the table together follows:

ALTER TABLE long_tab DROP obelisk long_pics;Multitable Inserts: instances The following example uses the multitable insert syntax come insert right into the sample table sh.sales some data from an entry table v a various structure.

See more: In The Production Cost Report, The Total, Access Denied


Note:

A variety of NOT NULL limit on the sales table have actually been disabled for functions of this example, since the instance ignores a variety 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 900The multitable insert statement looks prefer this:

INSERT ALL right 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) worths (product_id, customer_id, weekly_start_date+1, sales_mon) right 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) worths (product_id, customer_id, weekly_start_date+3, sales_wed) into sales (prod_id, cust_id, time_id, amount) worths (product_id, customer_id, weekly_start_date+4, sales_thu) right into sales (prod_id, cust_id, time_id, amount) worths (product_id, customer_id, weekly_start_date+5, sales_fri) right into sales (prod_id, cust_id, time_id, amount) worths (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 space the only rows in the sales table, the components now look prefer this:

SELECT * from sales order BY prod_id, cust_id, time_id; PROD_ID CUST_ID TIME_ID C PROMO_ID QUANTITY_SOLD quantity 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 900The next instances insert right into multiple tables. Mean you want to carry out to sales representatives some information on assignment of assorted sizes. The following instance creates tables because that small, medium, large, and special orders and also populates those tables with data native the sample table oe.orders:

CREATE TABLE small_orders (order_id NUMBER(12) no NULL, customer_id NUMBER(6) not NULL, order_total NUMBER(8,2), sales_rep_id NUMBER(6) );CREATE TABLE medium_orders AS choose * from small_orders;CREATE TABLE large_orders AS choose * from small_orders;CREATE TABLE special_orders (order_id NUMBER(12) not NULL, customer_id NUMBER(6) no 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 big orders:

INSERT ALL as soon as order_total 1000000 and order_total 2000000 THEN right into large_orders choose order_id, order_total, sales_rep_id, customer_id indigenous orders;You can accomplish the same thing utilizing the ELSE clause in location of the insert into the large_orders table:

INSERT ALL as soon as order_total 100000 and order_total The next example inserts right into the small, medium, and big tables, together in the coming before example, and additionally puts orders greater than 290,000 right into the special_orders table. This table likewise shows exactly how to use obelisk aliases to leveling the statement:

INSERT ALL when ottl 100000 and ottl 200000 THEN right into large_orders VALUES(oid, ottl, sid, cid) as soon as ottl > 290000 THEN right 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 native orders o, customers c whereby o.customer_id = c.customer_id;Finally, the next instance uses the an initial clause to put orders higher than 290,000 into the special_orders table and exclude those orders indigenous the large_orders table:

INSERT first WHEN ottl 100000 and ottl 290000 THEN right into special_orders once ottl > 200000 THEN right into large_orders VALUES(oid, ottl, sid, cid) pick 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 native orders o, client c where o.customer_id = c.customer_id;
*
Previous
*
Next

*
*
Home
*
Book List
*
Contents
*
Index
*
Master Index
*
Contact Us