Bulk insertion of records using BULK COLLECT and FORALL

Question

I am a beginner to PL/SQL and fetching huge amount of records from different tables and want to insert in to a one single table in PL/SQL using anonymous block with BULK COLLECT and FORALL. Could anybody help me out in deciding whether the following code is correct. I have referred many links

http://uksanjay.blogspot.com/2012/08/difference-between-bulk-collect-and.html?m=1

How do I use bulk collect and insert in Pl/SQl

There are two tables 'ABC' and 'BCD' from which records are fetched and inserted in to a destination table 'DEF'

ABC table (A,B,C ARE COLUMN NAMES)

A   B  C
1   X  Z1 
2   Y  Z2

BCD TABLE

A   B  C
1   X  Z1 
2   Y  Z2

In destination table 'DEF' I have to insert both the table records.

code is as follows:

DECLARE
TYPE FETCH_ARRAY IS TABLE OF A_CUR%ROWTYPE;   
A_ARRAY FETCH_ARRAY;
CURSOR A_CUR IS
    SELECT * FROM ABC
    UNION ALL 
    SELECT * FROM BCD;
BEGIN   
OPEN A_CUR;
LOOP                      
    FETCH A_CUR BULK COLLECT INTO A_ARRAY LIMIT 1000; 
        FORALL i IN 1..A_ARRAY.COUNT
        INSERT INTO DEF VALUES A_ARRAY(i); 

    EXIT WHEN A_CUR%NOTFOUND

END LOOP;
CLOSE A_CUR;
COMMIT;
END;

PS: select statements are more complex in the actual code, and source tables 'ABC' and 'DEF' consists of million records. So kindly help me in writing an efficient code.

Judging from [your follow-up question](https://stackoverflow.com/q/54604699/146325) you have already solved this and moved on to your real issue: using bulk operations across database link. — APC, Feb 09 '19 at 09:41
This could be written as a single `insert...select` statement. This would be a little more efficient and a lot simpler. — William Robertson, Feb 10 '19 at 13:06

score 0 · Answer 1 · answered Feb 11 '19 at 08:52

The optimal solution would be to rewrite your PL/SQL code into a single SQL INSERT INTO SELECT statement, like this:

INSERT INTO def
    SELECT * FROM abc
    UNION ALL
    SELECT * FROM bcd;

Note: if there exist some same records in both abcand bcd tables and you want only 1 record to be inserted in that situation then use UNION instead of UNION ALL.

Bulk insertion of records using BULK COLLECT and FORALL

1 Answers1