RE: Insert into 2 tables at one time

I am concern with the query  selecting from  seq_emp_id.currval.  Let's say 
there are 2 user insert into the same emp table. So 2 records are created ID 
221 and ID 222. How shall i know the currval id is accurate. Which shall i pick 
? 
 

Ken Naim <kennaim@xxxxxxxxx> wrote:
You are correct, I forgot that the sub query was required but the sequence
could still work.

INSERT ALL
INTO emp (empid, empname,deptno)
VALUES (seq_emp_id.nextval, empname,deptno) INTO dept 
(deptid,empid,deptname)
VALUES(deptid, seq_emp_id.currval, deptname)
Select :deptid deptid, :deptname deptname, :deptno deptno, :empname from
dual. 

I wouldn't use this unless I needed a query anyway to extract some of the
data.

-----Original Message-----
From: Gints Plivna [mailto:Gints.Plivna@xxxxxxxxx] 
Sent: Friday, March 18, 2005 9:16 AM
To: kennaim@xxxxxxxxx; jacintakean@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Insert into 2 tables at one time

First: Mutitable insert must use subquery.
Second initially thought of the same idea, but as from Oracle docs (look at
the last statement):
Restrictions on Multitable Inserts
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.

At least for 9i.
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/stat
ements_913a.htm

Gints

> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Ken Naim
> Sent: Friday, March 18, 2005 4:02 PM
> To: jacintakean@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: RE: Insert into 2 tables at one time
> 
> You can do it in one statement with something called a multi table
insert.
> 
> INSERT ALL
> INTO emp (empid, empname,deptno)
> VALUES (seq_emp_id.nextval, :empname,:deptno) INTO dept 
> (deptid,empid,deptname)
> VALUES(:deptid, seq_emp_id.currval, :deptname);
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
> On Behalf Of Kean Jacinta
> Sent: Friday, March 18, 2005 5:17 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Insert into 2 tables at one time
> 
> Hello
> 
> Need some help on this. I have 2 table
> 
> 
> Emp
> ----------
> empid (running on oracle sequenceno)
> empname
> deptid
> 
> Dept
> ----------
> deptid
> empid
> deptname
> 
> I need to insert into emp table first and then get seqid just created
and
> insert into dept table. Would it be possible to do tat ?
> 
> 
> 
> 
> 
> 
> 
> 
> __________________________________
> Do you Yahoo!?
> Make Yahoo! your home page
> http://www.yahoo.com/r/hs
> --
> http://www.freelists.org/webpage/oracle-l
> 
> --
> http://www.freelists.org/webpage/oracle-l

--
http://www.freelists.org/webpage/oracle-l

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

--
http://www.freelists.org/webpage/oracle-l

Other related posts: