CODEWITHSUNDEEP
sqloracle-databaseperformance
ken.ng
ken.ng

Reputation: 241

Best way to import a large dataset into Oracle sql database?

I'm new to oracle Database and I'm making a very simple bilingual dictionary ( 2 tables - English and French).

A dictionary often contains thousands of words. My question is that instead of writing thousand of [INSERT INTO ... VALUES] commands, is there any better way to somehow automate the process, like making a import form? Since the book my school provides only mentions about the INSERT command.

Upvotes: 0

Views: 2009

Answers (3)

Sam M
Sam M

Reputation: 4166

Oracle's Sql Loader product is great for quickly loading large amounts of data.

Upvotes: 0

Amit
Amit

Reputation: 465

If you have your data in a file, you can use the UTL_FILE package to read, parse and load your data. Oracle ULT_FILE package

You can use Oracle's external table feature as well. See here Oracle External tables

Upvotes: 0

bbrumm
bbrumm

Reputation: 1352

You can use a combination of a spreadsheet and an INSERT ALL command to quickly generate an SQL INSERT command to insert your data.

If you have your data in a spreadsheet format, you can use formulas to construct an INSERT statement for each row.

However, you could use the INSERT ALL syntax instead of the INSERT (single row) syntax.

To do this:

  1. Add a column into your spreadsheet with the formula that looks like this: "INTO table (col1, col2, col3) VALUES ('val1', 'val2', 'val3')". You would need to use concatenation to add the values into this formula.

So your formula may look like this (assuming columns of A, B, and C):

="INTO table (col1, col2, col3) VALUES ('"&A2&"', '"&B2&"', '"&C2&"')"

  1. Copy the formula to each row.
  2. Copy and paste all of these formulas to a query window inside your IDE.
  3. Add the words INSERT ALL at the start of your command
  4. Add the words SELECT * FROM dual; at the end of your command

Your command would then look like this:

INSERT ALL
INTO table (col1, col2, col3) VALUES ('val1', 'val2', 'val3')
INTO table (col1, col2, col3) VALUES ('val1a', 'val2a', 'val3a')
INTO table (col1, col2, col3) VALUES ('val1b', 'val2b', 'val3b')
SELECT * FROM dual;

This will insert all records in a single statement and is likely to be much faster than hundreds or thousands of INSERT statements.

Alternatively, you could use a tool like Data Pump Import and Export, but I have limited experience with that so perhaps another user could elaborate on that.

Upvotes: 1

Related Questions