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.

blackfunk@verizon.net 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.

How about just not mapping the data you don't want to insert?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

blackfunk@verizon.net 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.
>

Another option is to create an External Table which reads this text
file. You can then use any SQL statements you want to read data from the
External Table and insert into any table.

HTH,
Brian


--
================================================== =================

Brian Peasland
dba@nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

--
Posted via a free Usenet account from http://www.teranews.com

On Sep 29, 11:25 am, DA Morgan <damor...@psoug.org> wrote:
> blackf...@verizon.net 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.
>
> How about just not mapping the data you don't want to insert?
> --
> Daniel A. Morgan
> University of Washington
> damor...@x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

The problem is I have to insert these values. Basically it's like a
sentence that continues to the next line. I send the first wave
through then another row to insert different data in a few select
columns in the next. It's possible to do this but the problem in my
case is that the primary key is a sequence and eventually it runs into
a constraint error. I was hoping there would be some technical way to
get around this but it sounds like I am going to have to split the ctl
file and run a script to execute them both. It sucks but hey unless
someone knows a way this can work.

> The problem is I have to insert these values. Basically it's like a
> sentence that continues to the next line. I send the first wave
> through then another row to insert different data in a few select
> columns in the next. It's possible to do this but the problem in my
> case is that the primary key is a sequence and eventually it runs into
> a constraint error. I was hoping there would be some technical way to

How does a sequence eventually run into a constraint error?

On Sep 30, 12:50 pm, csn...@gmail.com wrote:
> > The problem is I have to insert these values. Basically it's like a
> > sentence that continues to the next line. I send the first wave
> > through then another row to insert different data in a few select
> > columns in the next. It's possible to do this but the problem in my
> > case is that the primary key is a sequence and eventually it runs into
> > a constraint error. I was hoping there would be some technical way to
>
> How does a sequence eventually run into a constraint error?

I believe it's b/c it only insert x amount of rows at a time for each
into table statement. I am loading 50000+ rows and the constraint
error happens after 64 rows are inserted. So, it's as if it it is
starting the sequence over in the next into table statement. This
sequence is not an object it is a dynamic sequence generated at
execution.

On Sep 28, 9:30 pm, blackf...@verizon.net 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' odd-
numbered 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

On Oct 1, 9:22 am, blackf...@verizon.net wrote:
> On Sep 30, 12:50 pm, csn...@gmail.com wrote:
>
> > > The problem is I have to insert these values. Basically it's like a
> > > sentence that continues to the next line. I send the first wave
> > > through then another row to insert different data in a few select
> > > columns in the next. It's possible to do this but the problem in my
> > > case is that the primary key is a sequence and eventually it runs into
> > > a constraint error. I was hoping there would be some technical way to
>
> > How does a sequence eventually run into a constraint error?
>
> I believe it's b/c it only insert x amount of rows at a time for each
> into table statement. I am loading 50000+ rows and the constraint
> error happens after 64 rows are inserted. So, it's as if it it is
> starting the sequence over in the next into table statement. This
> sequence is not an object it is a dynamic sequence generated at
> execution.

Please read David's remarks carefully and completely. Then if you
still insist on splitting the rows this way, you'll need two steps.
1. Load the data into a staging table.
2. Then split the data into the destination table via a PL/SQL
program.

Outside of that, I think the PK you propose will not really work. you
really need two columns as the Primary key. The first column would be
your sequence, then your second column would be oh, let's call it
part#. Then the PL/SQL program can generate the two rows where the
column A has the same value and column part# has values like 'a' and
'b'

It is a poor design and you will really be better off with two
tables. I just gave you enough rope to hang yourself, so be careful!

Ed
(PS and don't blame me your design fails! you were warned.)