5

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#

Jon
  • 38,814
  • 81
  • 233
  • 382

3 Answers3

10

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

Community
  • 1
  • 1
Vincent Malgrat
  • 66,725
  • 9
  • 119
  • 171
  • If I wanted to do an update of one row could I do an inner join on the same table or would that not work – Jon Sep 05 '11 at 10:29
  • The typical scenario is that you have many rows to load. Each row will be either inserted if new, or updated into the target table. This is the standard case for a MERGE statement. It's not clear if it's applicable in your case. – Vincent Malgrat Sep 05 '11 at 11:23
  • Is there a way to use SQL*Loader when the records are in-memory and not in a file? Say, for example, in a spark data frame. – Kumar Vaibhav Apr 03 '18 at 02:01
6

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.

Kevin Burton
  • 11,676
  • 2
  • 24
  • 37
3

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

  1. Prepare your data in a tabular format.
  2. Let's say you have three columns, A (text), B (number) & C (date). In the D column, 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'));"

Nik Reiman
  • 39,067
  • 29
  • 104
  • 160
Shaakunthala
  • 107
  • 2
  • 9