Bulk Insert and Update and Update at the same time

Question

I am currently inserting data from a file into a table. I format the data before inserting it into the table (Remove tabs/carriage returns/get char codes for certain text etc). Firstly I have to check if a record exists and update it if it does. If it doesn't that record must be inserted into the table.

The problem I am having is it takes an extremely long time. I have two files, one with just below 500k records and one with above 4mil. If it possible to run a bulk collect so that I does both?

E.G.

open c_cur;
loop
  fetch c_cur bulk collect into examp limit 50000;
  exit when limit.count = 0
  forall x in 1..limit.count
    update table1
    set...
    where...

    if sql%notfound then
      insert into table1
      values (...)
    end if;
  commit;
end loop;
close c_cur;

Is this possible?

I also heard about setting up a staging table and dumping the file in there and then working from there. Is that the better option? How much of a performance increase would that give me?

I would also prefer to not do a merge on a table from a file.

Thanks.

You can use a combination of forall and merge. See [here](http://www.morganslibrary.org/reference/plsql/array_processing.html#apfm) — Aramillo, Jan 26 '15 at 13:13

Boneist · Answer 1 · 2015-01-26T14:32:00.900

The best way to do this would be to set up external tables based on the files (you'll have to make sure that the database server can see the directory that the files are stored in). That way, you can then do a MERGE statement (no need for bulk collects or the like; that's still a row-by-row approach, even if it's slightly more efficient) directly against the external tables.

The next best (IMHO) would be to load the data from the files into a staging table and then do the merge statement based on the staging table.

score 0 · Answer 2 · answered Jan 26 '15 at 15:11

You can use sql%bulk_rowcount

--after forall update:
for i in 1..examp.count loop
  if sql%bulk_rowcount(i) > 0 then
    examp.delete(i); -- record already updated - remove it from array
  end if;
end if;

-- loop only non-deleted elements here
forall i in indicies of examp
  insert into ....

Bulk Insert and Update and Update at the same time

2 Answers2