Re: Best practice for loading data into relational tables

On Mar 18, 4:14 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Gints Plivna wrote:
> > I agree with others that SQLLoader is the way to go. However that
> > won't help you much with fk errors, unless of course you disable them
> > in advance. So here you can use intermediatte tables without any FK
> > logic just load data in them. After succesful loading use insert
> > into ... select from... And here you can quite easily insert firstly
> > the very parent rows then children, then grandchildren. Of course
> > there might be some circular FKses but this is quite unusual case,
> > then you'd have to either disable a few FKses or make them deferred
> > and commit only at the very end of insert process when everything is
> > OK.
>
> > Gints Plivna
> >http://www.gplivna.eu
>
> Or use deferrable constraints which would likely be better than
> disabling default immediate constraints.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

thanks so much for the really good suggestions.

I think I got a lot of viable solutions, I did not know about Toad supporting Excel data natively. The only thing is Toad is a paid software!
But I think what I am planning to do is a combination of disabling constraints and using sql*loader to take care of the PK & FK. Unfortunately I think I have the rare case of circular FK's, but disabling the constraints seems to be the way to go for now.

I also liked the idea of putting the insert into & insert into (select from) into a shell script with debugging info (just echo). I am pretty adept at shell scripts & awk so it looks attractive, but at the same time it is a lot of unncessary code and I think the sql*loader option is equally easy to do.

Thanks to all and really appreciate the solutions suggested by each of you.

Anoop Received on Tue Mar 18 2008 - 19:51:23 CDT