| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Insert into 2 tables at one time
Oh ........ i see . :) THank You so much .
JKean
"Reidy, Ron" <Ron.Reidy_at_arraybiopharma.com> wrote:
Currval is session dependent.
-----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 9:14 AM
To: kennaim_at_gmail.com; oracle-l_at_freelists.org
Subject: 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 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 This electronic message transmission is a PRIVATE communication which contains information which may be confidential or privileged. The information is intended to be for the use of the individual or entity named above. If you are not the intended recipient, please be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. Please notify the sender of the delivery error by replying to this message, or notify us by telephone (877-633-2436, ext. 0), and then delete it from your system. --------------------------------- Do you Yahoo!? Yahoo! Small Business - Try our new resources site! -- http://www.freelists.org/webpage/oracle-lReceived on Fri Mar 18 2005 - 11:24:47 CST
 |
 |