Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to bulk insert data into Oracle database

I am going to create a lot of data scripts such as INSERT INTO and UPDATE

There will be 100,000 plus records if not 1,000,000

What is the best way to get this data into Oracle quickly? I have already found that SQL Loader is not good for this as it does not update individual rows.

Thanks

UPDATE: I will be writing an application to do this in C#

like image 542
Jon Avatar asked Sep 05 '11 09:09

Jon


2 Answers

Load the records in a stage table via SQL*Loader. Then use bulk operations:

  • INSERT INTO SELECT (for example "Bulk Insert into Oracle database")
  • mass UPDATE ("Oracle - Update statement with inner join")
  • or a single MERGE statement
like image 130
Vincent Malgrat Avatar answered Oct 19 '22 10:10

Vincent Malgrat


To keep It as fast as possible I would keep it all in the database. Use external tables (to allow Oracle to read the file contents), and create a stored procedure to do the processing.

The update could be slow, If possible, It may be a good idea to consider creating a new table based on all the records in the old (with updates) then switch the new & old tables around.

like image 32
Kevin Burton Avatar answered Oct 19 '22 08:10

Kevin Burton