I am not sure if I can word this right. But I am trying to explore the
best way of loading data into about 40-50 oracle tables. We have
collected the data through various means / formats - some are in text
files, excel, some that we know need to be arranged etc..

The way we are doing it now, is to write insert statements for every
line of data. So the actual arrangement of the data and writing the
insert sql's are happening simultaneously. But there are many issues
in writing insert sql's. The primary being debugging, - once I run the
file containing all inserts, I get "1 row loaded" - doesnt really tell
me which sql ran - each line runs independent of the row above or
below it, the file just helps me run them all together, that is all.
The other issue (related to the first) is if the tables are related
then I need to order my inserts so that the primary key gets inserted
before the child/foreign keys. This is a very manual process and I do
not think this can be automated much.

So I am thinking probably my approach is wrong - maybe I should deal
with the data load in a different manner - is there any best practice
that I could use? How have you guys handled data loads of medium size
~10000 rows going into 40 tables that are related to one another?

Thanks.

On Sat, 15 Mar 2008 23:19:16 -0700 (PDT), Anoop
<anoopkumarv (AT) gmail (DOT) com> wrote:

One word: sql*loader.
Can load data into several tables at once.
Reinventing a wheel which Oracle already provides in every release of
Oracle will not only be very costly, but also waste of resources.

--

Sybrand Bakker
Senior Oracle DBA

On Sat, 15 Mar 2008 23:19:16 -0700 (PDT), Anoop
<anoopkumarv (AT) gmail (DOT) com> wrote:

One word: sql*loader.
Can load data into several tables at once.
Reinventing a wheel which Oracle already provides in every release of
Oracle will not only be very costly, but also waste of resources.

--

Sybrand Bakker
Senior Oracle DBA

On Sat, 15 Mar 2008 23:19:16 -0700 (PDT), Anoop
<anoopkumarv (AT) gmail (DOT) com> wrote:

One word: sql*loader.
Can load data into several tables at once.
Reinventing a wheel which Oracle already provides in every release of
Oracle will not only be very costly, but also waste of resources.

--

Sybrand Bakker
Senior Oracle DBA

On Sat, 15 Mar 2008 23:19:16 -0700 (PDT), Anoop
<anoopkumarv (AT) gmail (DOT) com> wrote:

One word: sql*loader.
Can load data into several tables at once.
Reinventing a wheel which Oracle already provides in every release of
Oracle will not only be very costly, but also waste of resources.

--

Sybrand Bakker
Senior Oracle DBA

On 16 Mrz., 07:19, Anoop <anoopkum... (AT) gmail (DOT) com> wrote:
How have you guys handled data loads of medium size
as Sybrand stated, SQL Loader is number one choice.

http://www.orafaq.com/wiki/SQL%2ALoader_FAQ

http://www.psoug.org/reference/sqlloader.html

best regards
Markus Tazl

On 16 Mrz., 07:19, Anoop <anoopkum... (AT) gmail (DOT) com> wrote:
How have you guys handled data loads of medium size
as Sybrand stated, SQL Loader is number one choice.

http://www.orafaq.com/wiki/SQL%2ALoader_FAQ

http://www.psoug.org/reference/sqlloader.html

best regards
Markus Tazl

On 16 Mrz., 07:19, Anoop <anoopkum... (AT) gmail (DOT) com> wrote:
How have you guys handled data loads of medium size
as Sybrand stated, SQL Loader is number one choice.

http://www.orafaq.com/wiki/SQL%2ALoader_FAQ

http://www.psoug.org/reference/sqlloader.html

best regards
Markus Tazl

On 16 Mrz., 07:19, Anoop <anoopkum... (AT) gmail (DOT) com> wrote:
How have you guys handled data loads of medium size
as Sybrand stated, SQL Loader is number one choice.

http://www.orafaq.com/wiki/SQL%2ALoader_FAQ

http://www.psoug.org/reference/sqlloader.html

best regards
Markus Tazl

Anoop wrote:
I'd vote SQL*Loader is almost all cases though given the small number of
rows you might want to look at using an External Table.
http://www.psoug.org/reference/externaltab.html
Which is really just another implementation of SQL*Loader.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org