Re: Best practice for loading data into relational tables

Anoop wrote:
> 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.

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_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org