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 .
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 .
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Although I used SQL*Loader for years and have an affection for it, external tables are more natural for the SQL developer.
Here is a link to the manual which describes the scenarios where external tables are useful:
https://docs.oracle.com/database/121/SUTIL/GUID-289C0B1B-96FE-430B-8525-AC077E434DA9.htm#SUTIL1000
In terms of efficiency and performance, my understanding bus that the underlying mechanisms which support external tables are the same as those which support SQLloader.
External tables are elegant, simple, and considerably easier to learn and implement, and have a more "natural" SQL feel about them.
But basically it depends on a few factors:
Performance - in many cases there is little difference. Test them both and decide which works for you.
Ease of implementation - Most developers will find external tables easier to implement.
Ease of support - I don't think there's much in it, bit your support team might disagree.
Fit for purpose - so this is where they might differ depending on the functionality you need to leverage.
So personally, I think it is useful to know both, but I'd lean towards external tables for implementing these days.
JT
Here is a link to the manual which describes the scenarios where external tables are useful:
https://docs.oracle.com/database/121/SUTIL/GUID-289C0B1B-96FE-430B-8525-AC077E434DA9.htm#SUTIL1000
In terms of efficiency and performance, my understanding bus that the underlying mechanisms which support external tables are the same as those which support SQLloader.
External tables are elegant, simple, and considerably easier to learn and implement, and have a more "natural" SQL feel about them.
But basically it depends on a few factors:
Performance - in many cases there is little difference. Test them both and decide which works for you.
Ease of implementation - Most developers will find external tables easier to implement.
Ease of support - I don't think there's much in it, bit your support team might disagree.
Fit for purpose - so this is where they might differ depending on the functionality you need to leverage.
So personally, I think it is useful to know both, but I'd lean towards external tables for implementing these days.
JT
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial