Most INSERT statements are the single-table variety, but Oracle also supports a multiple-table INSERT statement. With a multitable insert, you can make a single pass through the source data and load the data into more than one table.
If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.
By using this multitable INSERT statement instead of two separate statements, the code makes a single pass through the all_objects table instead of two and thus loads the data more faster.
[ ALL | FIRST ]
WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
[WHEN condition THEN insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]...
[ELSE insert_into_clause [values_clause]
[insert_into_clause [values_clause]]...
]If a WHEN condition evaluates to TRUE, the corresponding INTO clause is executed. If no WHEN condition evaluates to TRUE, the ELSE clause is executed. The keyword ALL tells the database to check each WHEN condition. On the other hand, the keyword FIRST tells the database to stop checking WHEN conditions after finding the first TRUE condition.
insert first
when mod( object_id, 2 ) = 1 then into t1 ( x, y ) values (object_id, created )
when mod( object_id, 2 ) = 0 then into t2 ( a, b ) values ( object_id, created )
select object_id, created from all_objects
By using this multitable INSERT statement instead of two separate statements, the code makes a single pass through the all_objects table instead of two and thus loads the data more faster.
07 Mar 2006 11:52:00
thanks,
Is it possible in MS SQL Server?
Can I do either an updation or an insertion after a case checking?
23 Mar 2006 08:37:57
But how can i do this in my query:
I want to insert a SEQUENCE with values in the New table.
--
insert into MerchantSettlement (Settlementid, Merchantid, Settlementamt)
values select settlementid_nextval as Settlementid, t.merchantid as Merchantid,
t.transactionamount as Settlementamt
from TransactionHistory t
--
I am getting error:
ORA-00936: missing expression
Please help me out in this
23 Mar 2006 18:30:28
Take off the word "values" and it should work:
insert into MerchantSettlement (Settlementid, Merchantid, Settlementamt) select settlementid_nextval as Settlementid, t.merchantid as Merchantid, t.transactionamount as Settlementamt from TransactionHistory t
The right syntax of INSERT is:
INSERT INTO <table or expression> <alias> (column,?) <values_clause>|<subquery>
RETURNING <exprs> INTO <data_items>;
HTH.
01 Apr 2007 16:36:09
try to see:
http://www.merovingio.it/or...
bye
16 Apr 2007 18:14:12
Nice help,
thank you