Bulk Data insert excluding the duplicates

[rohit54321]

Hi,

I have a tmp_table with say 10,000 records and a main_table with say 50 million records..

Few records of tmp_table are already existing in main_table so i need to insert only the new records into the main_table.

I have a primary key in both the tables;

I am running a daemon which does this constantly.

Can anyone please help me to find an efficient solution.

I tried PL/SQL exception handling, the MINUS... any other way to do it.

Thanks in Advance.

Rohit

[amitpatel66]

Hi,

I have a tmp_table with say 10,000 records and a main_table with say 50 million records..

Few records of tmp_table are already existing in main_table so i need to insert only the new records into the main_table.

I have a primary key in both the tables;

I am running a daemon which does this constantly.

Can anyone please help me to find an efficient solution.

I tried PL/SQL exception handling, the MINUS... any other way to do it.

Thanks in Advance.

Rohit

Try this:

1. Make use of a CURSOR to hold the records that are not inserted in main table
2. use FORALL BULK INSERT to insert the records from CURSOR in to MAIN TABLE (LIMIT to 1000)
3. COMMIT
4. Repeat STEP 2 for every 1000 RECORDS

[rohit54321]

Try this:

1. Make use of a CURSOR to hold the records that are not inserted in main table
2. use FORALL BULK INSERT to insert the records from CURSOR in to MAIN TABLE (LIMIT to 1000)
3. COMMIT
4. Repeat STEP 2 for every 1000 RECORDS

But how do i handle duplicate records.
The first step is the problem. how do i get the
set of records that are not inserted yet.

[amitpatel66]

But how do i handle duplicate records.
The first step is the problem. how do i get the
set of records that are not inserted yet.

How about this code:

 
DECLARE
TYPE tempdata IS TABLE of temp_table%ROWTYPE;
td temp_data;
CURSOR temp_data IS SELECT * FROM temp_table WHERE <primary_key_column> NOT IN (SELECT <primary_key_column> FROM main_table);
BEGIN
OPEN temp_data;
LOOP
FETCH temp_data BULK COLLECT INTO td LIMIT 1000;
FORALL I IN td.FIRST..td.LAST 
INSERT INTO main_table td(I);
COMMIT;
EXIT WHEN temp_data%NOTFOUND;
END LOOP;
END;
 

Note: Not Tested!!

[rohit54321]

How about this code:

 
DECLARE
TYPE tempdata IS TABLE of temp_table%ROWTYPE;
td temp_data;
CURSOR temp_data IS SELECT * FROM temp_table WHERE <primary_key_column> NOT IN (SELECT <primary_key_column> FROM main_table);
BEGIN
OPEN temp_data;
LOOP
FETCH temp_data BULK COLLECT INTO td LIMIT 1000;
FORALL I IN td.FIRST..td.LAST 
INSERT INTO main_table td(I);
COMMIT;
EXIT WHEN temp_data%NOTFOUND;
END LOOP;
END;
 

Note: Not Tested!!

this should work, I will have to tweak it a bit

thanks a lot.

[amitpatel66]

this should work, I will have to tweak it a bit

thanks a lot.

Yes, the Cursor will get you the info which is not there in main table that you can happily then insert into main table.

Do post back in case of any further issues.

[Dave44]

this should work, I will have to tweak it a bit

thanks a lot.

I agree, bulk processing is a great way to go. I would just change the cursor query to use an anti-join instead of the not in; the anti-join will perform faster almost always, especially with large tables.

[amitpatel66]

I agree, bulk processing is a great way to go. I would just change the cursor query to use an anti-join instead of the not in; the anti-join will perform faster almost always, especially with large tables.

I have noticed problem many times while using an ANTI-JOIN producing incorrect results. Using SET operations is another way of doing it.