RE: Insert into 2 tables at one time
- From: Kean Jacinta <jacintakean@xxxxxxxxx>
- To: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
- Date: Fri, 18 Mar 2005 08:19:21 -0800 (PST)
Oh ........ i see . :) THank You so much .
JKean
"Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx> wrote:
Currval is session dependent.
-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Kean Jacinta
Sent: Friday, March 18, 2005 9:14 AM
To: kennaim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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@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
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!
--
//www.freelists.org/webpage/oracle-l
Other related posts:
