RE: need proc for: insert into table1 select * from table2

How about this:
declare
cursor sourcetablename_cur is
select * from sourcetablename;

vn_count number := 0;
begin
for sourcetablename_rec in sourcetablename_cur loop insert into targettablename values (sourcetablename_rec); count := count + 1;
if count = 1000 then
commit;
count := 0;
end if;
end loop;
end;

That's all I'm doing at Nike.

I need one of those bumper stickers that say: I'D RATHER BE DBA'ing. ;)

Don't forget about ora 1555. That should be all I have to say.

No smily face there, that error deserves a frown. :(

Lyall Barbour
Current Oracle PL/SQL programmer at Nike, but wants to go back to Administration.

See ya!

------Original Message------
From: "Ben Poels" <poelsb_at_post.queensu.ca> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: May 4, 2000 7:35:19 PM GMT
Subject: need proc for: insert into table1 select * from table2

Hi,

Does anyone have a procedure that will do an
"insert into table1 select * from table2" and
commit after every 1000 rows?
This is not a copy table, so I cannot use
"create table as" since I want to insert the
entire table2 into a table1 that already has a large number of rows. Both tables are in the same database.
The SQL*Plus manual suggests not using the
"copy from....to" for copying from one Oracle
database to another. Why? Is it very inefficient/slow? Thanks,

Ben Poels
Queen's University
Kingston, Ontario

--
Author: Ben Poels
INET: poelsb_at_post.queensu.ca

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

______________________________________________
FREE Personalized Email at Mail.com