Geek Answers Handbook

Best way to bulk insert data into an Oracle database

I am going to create many data scripts like INSERT INTO and UPDATE

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

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

thanks

UPDATE: I will write an application for this in C #

+4
source share
3 answers

Load records into the stage table using SQL * Loader . Then use bulk operations:

+9
source

To save this as quickly as possible, I would save all this in a database. Use external tables (to allow Oracle to read the contents of the file), and create a stored procedure for processing.

Updating may be slow. If possible, it might be a good idea to consider creating a new table based on all the records in the old (with updates), and then switching new and old tables.

+6
source

How about using a spreadsheet program like MS Excel or LibreOffice Calc? This is how I do volume inserts.

  • Prepare your data in tabular format.
  • Say you have three columns: A (text), B (number) and C (date). In column D, enter the following formula. Adjust accordingly.

="INSERT INTO YOUR_TABLE (COL_A, COL_B, COL_C) VALUES ('"&A1&"', "&B1&", to_date ('"&C1&"', 'mm/dd/yy'));"

+3
source

More articles:


All Articles