RE: A question on table export
- From: Asif Momen <asif_oracle@xxxxxxxxx>
- To: patrick.elliott@xxxxxxxxxxxxx, "anuragdba@xxxxxxxxx" <anuragdba@xxxxxxxxx>, ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 1 Jul 2008 09:47:43 -0700 (PDT)
Hi,
> Can we export a table and import into another table (which has the same
structure)?
> Both the tables are in the same schema.
No, not into another table. Why not insert directly into the destination table
like:
insert into dest select * from source;
Regards
Asif Momenhttp://momendba.blogspot.com
--- On Tue, 7/1/08, Elliott, Patrick <patrick.elliott@xxxxxxxxxxxxx> wrote:
From: Elliott, Patrick <patrick.elliott@xxxxxxxxxxxxx>
Subject: RE: A question on table export
To: "Elliott, Patrick" <patrick.elliott@xxxxxxxxxxxxx>, "anuragdba@xxxxxxxxx"
<anuragdba@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
Date: Tuesday, July 1, 2008, 7:40 AM
I haven't worked with bulk inserts much, so anyone who
has please feel free to respond with an improved solution using bulk
inserts.
Pat
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Elliott,
Patrick
Sent: Tuesday, July 01, 2008 9:28 AM
To:
anuragdba@xxxxxxxxx; ORACLE-L
Subject: RE: A question on table
export
The only reason that I can think of to import into the
same schema is to maybe prevent the rollback segment from filling up undo or
you
are getting snapshot too old. If you are having trouble with too many rows
on the insert, then you just need to code the insert in pl/sql with
intermediate
commits.
DECLARE
CURSOR c1 IS
SELECT *
FROM
source_table;
row_cnt NUMBER := 0;
BEGIN
FOR r1 IN c1 LOOP
INSERT INTO
destination_table
VALUES (r1.col1, r1.col2, ...
r1.coln);
row_cnt := row_cnt
+1;
IF row_cnt > 1000
THEN
COMMIT;
row_cnt :=
0;
END IF;
END LOOP;
COMMIT;
END;
/
Just change the 1000 to a higher or lower number
depending on your requirements. Change source_table and destination_table
to the real table names and col1, col2, .. to the real column
names.
Pat
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Anurag
Verma
Sent: Monday, June 30, 2008 6:10 PM
To:
ORACLE-L
Subject: A question on table export
I have an Oracle 9i (9.2.0.7.0) database.
One question on table export.
Can we export a table and import into another table (which has the same
structure)?
Both the tables are in the same schema.
Thanks,
--
Anurag Verma,
Database Administrator
ERCOT(Electric
Reliability Council of Texas),
Texas 76574
[CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is
proprietary to Medtronic and is intended for use only by the individual or
entity to which it is addressed, and may contain information that is private,
privileged, confidential or exempt from disclosure under applicable law. If you
are not the intended recipient or it appears that this mail has been forwarded
to you without proper authority, you are notified that any use or dissemination
of this information in any manner is strictly prohibited. In such cases, please
delete this mail from your records. To view this notice in other languages you
can either select the following link or manually copy and paste the link into
the address bar of a web browser:
http://emaildisclaimer.medtronic.com
Other related posts:
