Avatar of MIHIR KAR
MIHIR KAR
Flag for India asked on

Loading records from external file-system to DB!

Hi Expert,

I want to insert multiple records on a single query and my records are in an external file.

Please any advice for me ...

I have 2 Approach:: Please suggest which one will work effectively.

1: Using external table ... "insert into table select * from exttbl;"
2: Using SQL loder .
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Mark Geerlings

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
jtrifts

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
johnsone

External tables are just temporary tables loaded with SQL*Loader.

The answer is really where does the file reside?  If it is not on the database server, external table is probably not the solution, the database needs direct access to the file in order to load it.
Mark Geerlings

I agree that both of these approaches can work.  I have also used both of them at different times.  Neither one actually uses a query to get the job done.  Johnsone's comment about where the data file is located, is very important.

There is a third option that can also be used to load data into Oracle tables, that is a PL\SQL procedure that uses the procedures and functions in UTL_FILE to read an ASCII file (on the server) then convert or transform the data if necessary and load it into one or more tables.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck