RE: Insert into 2 tables at one time
- From: Kean Jacinta <jacintakean@xxxxxxxxx>
- To: kennaim@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 18 Mar 2005 08:13:43 -0800 (PST)
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
> --
> //www.freelists.org/webpage/oracle-l
>
> --
> //www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
