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_at_gmail.com> 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_at_softex.lv] Sent: Friday, March 18, 2005 9:16 AM
To: kennaim_at_gmail.com; jacintakean_at_yahoo.com; oracle-l_at_freelists.org 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_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Ken Naim
> Sent: Friday, March 18, 2005 4:02 PM
> To: jacintakean_at_yahoo.com; oracle-l_at_freelists.org
> 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_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Kean Jacinta
> Sent: Friday, March 18, 2005 5:17 AM
> To: oracle-l_at_freelists.org
> 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