Re: Best practice for loading data into relational tables

On Sat, 15 Mar 2008 23:19:16 -0700 (PDT), Anoop <anoopkumarv_at_gmail.com> 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.

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