Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: SQL*LOADER, using multiple intos for the same table

Re: SQL*LOADER, using multiple intos for the same table

From: <>
Date: Mon, 01 Oct 2007 06:50:11 -0700
Message-ID: <>

On Sep 28, 9:30 pm, wrote:
> Hi,
> I have a file of data for a table without enough columns. So, i have
> to create another row to completely import the data for one row. I
> know the logical step is to create more columns but I cannot for other
> reasons. So, here is what I am doing:
> append
> into table x
> column a sequence (max,1)
> column b pos 1:3
> column c pos 4:6
> column d pos 7:9
> into table x
> column a sequence (max,2)
> column b pos 13:14,
> column c pos 15:16
> I am trying to grab data in another part of the record and insert it
> into the same column as the initial insert. The problem is column a is
> the primary key and I keep getting unique constraint errors. So, I am
> trying to figure out is there an option that allows the first into to
> completely finish its inserts and then attacks the second into
> statement OR do I create a sequence and use the nextval attribute for
> both INTO's. Please help, I am stuck. Thx.

This appears to be quite a mess you're making (through no fault of your own, but through 'design'). As I read this you have a table to load which has four columns, yet you have six columns of data to load for each row. Splitting this into two rows creates yet another problem: how do you associate data in row 'n+1' with the parent record in row 'n'? There is no 'reference' data to link these records as one set of data, and you're generating a 'sequence' via SQL*Loader which won't do what you need (and, yes, it resets itself for each new INTO statement, thus your constraint violation errors). Using a database sequence won't help here, as you'll simply generate a new pk value for each insert which will eliminate your constraint violations but it won't necessarily 'link' your data rows together as row x+1 may not load and the next 'parent' row will get sequence number x+1 rather than x+2 as you'd intended. Without a table redesign this won't work, again as you cannot rely upon the sequence numbers to 'associate' oddnumbered  pk rows with the even-numbered pk row immediately following (presuming all of your 'parent' rows are odd-numbered sequence values).

This is an accident which has already happened. There is no fix other than to correctly define the table for the data set in question. Your other option has already been given to you: map only the data you need into the columns you have. Unfortunately if you need all of the data in the file your only viable choice is to have the table defined properly. No other kludge will work.

David Fitzjarrell Received on Mon Oct 01 2007 - 08:50:11 CDT

Original text of this message