Insert a text file into Oracle with Bulk Insert

Question

I have a place.file text file;

place.file

• New Hampshire
• New Jersey
• New Mexico
• Nevada
• New York
• Ohio
• Oklahoma ....

There are 4000 place names in this file. I will match my my_place table in oracle and place.file . So I want to insert the place.file into the Oracle . Maybe I should use bulk insert, how can I do bulk insert ?

Answer 1

No mention of an Oracle version. (For the best possible answer, always include Oracle version, Oracle edition, OS, and OS version.)

However, you should investigate using an external table for this purpose. Once you have that set up correctly, you can do:

insert into db_table select ... from external_table;

Optionally, you could use the APPEND hint on the INSERT statement, to use direct load. Also,optionally, you could set the NOLOGGING attribute on the table you're loading the data into, for best performance. But, consider the recovery implications before you enable NOLOGGING.

Hope that helps,

-Mark

Answer 2

You can use SQL Loader from Oracle.

The syntax is:

sqlldr *connection_string* control=*control_file.ctl*

The control file contains:

LOAD DATA
INFILE names.file
INTO TABLE <table_name>
FIELDS TERMINATED BY <delimiter>
OPTIONALLY ENCLOSED BY <enclosing character>
(<column_name>[, <column_name>, <column_name>])