Re: Best practice for loading data into relational tables
Date: Sun, 16 Mar 2008 11:27:30 -0700
> 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?
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.orgReceived on Sun Mar 16 2008 - 13:27:30 CDT