RE: Insert into 2 tables at one time
- From: "Reidy, Ron" <Ron.Reidy@xxxxxxxxxxxxxxxxxx>
- To: <jacintakean@xxxxxxxxx>, <kennaim@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Mar 2005 09:18:29 -0700
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 ?=20
=20
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=20
(deptid,empid,deptname)
VALUES(deptid, seq_emp_id.currval, deptname)
Select :deptid deptid, :deptname deptname, :deptno deptno, :empname from
dual.=20
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]=20
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.=20
You cannot perform a multitable insert into a remote table.=20
You cannot specify a table collection expression when performing a
multitable insert.=20
In a multitable insert, all of the insert_into_clauses cannot combine to
specify more than 999 target columns.=20
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.=20
Plan stability is not supported for multitable insert statements.=20
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
>=20
> You can do it in one statement with something called a multi table
insert.
>=20
> INSERT ALL
> INTO emp (empid, empname,deptno)
> VALUES (seq_emp_id.nextval, :empname,:deptno) INTO dept=20
> (deptid,empid,deptname)
> VALUES(:deptid, seq_emp_id.currval, :deptname);
>=20
>=20
> -----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
>=20
> Hello
>=20
> Need some help on this. I have 2 table
>=20
>=20
> Emp
> ----------
> empid (running on oracle sequenceno)
> empname
> deptid
>=20
> Dept
> ----------
> deptid
> empid
> deptname
>=20
> 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 ?
>=20
>=20
>=20
>=20
>=20
>=20
>=20
>=20
> __________________________________
> Do you Yahoo!?
> Make Yahoo! your home page
> http://www.yahoo.com/r/hs
> --
> //www.freelists.org/webpage/oracle-l
>=20
> --
> //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=20
http://mail.yahoo.com=20
--
//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=20
to be for the use of the individual or entity named above. If you are =
not the=20
intended recipient, please be aware that any disclosure, copying, =
distribution=20
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
