Assuming that the resource_id column is the PK (or at least unique) of the
RQMT table something like this should work (albeit slowly). Its missing some
definitions, but this should give you an idea on how you _might_ proceed
Kevin
DECLARE
RowCount NUMBER := 0;
MAX NUMBER := -99999999;
CURSOR c_src (id IN NUMBER) IS
SELECT *
FROM (SELECT resource_id, classification
FROM rqmt
WHERE resource_id > id
ORDER BY resource_id
)
WHERE rownum <= 1000;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount > 0 THEN
LOOP
OPEN c_src(max);
FETCH c_src BULK COLLECT INTO tbl_resource_id,
tbl_classification;
IF (c_src%ROWCOUNT = 0) THEN
EXIT LOOP;
END IF;
Max = tbl_resource_id(tbl_resource_id.MAX)
FORALL j IN tbl_resource_id.FIRST .. tbl_resource_id.LAST
INSERT INTO RESOURCE VALUES
tbl_resource_id(j), tbl_classification(j);
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || sql%Rowcount || '
Rows transitioned.');
COMMIT;
CLOSE c_src;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
-----Original Message-----
Sent: Thursday, October 16, 2003 11:55 AM
To: Multiple recipients of list ORACLE-L
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER := 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount > 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kevin Toepke
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
