Re: Table Question

It is not always possible to disable production constraints, nor is it wise to do so unless one can guarantee that no other user would have access during that time....

With that in mind, there are a couple ways to get the transaction records inserted.

1. write the insert statement so that it only chooses those transaction records which do not have duplicate sequences in the production table
2. write a PL/SQL block to fetch the transactions and test production before executing the insert statement.

If these sequence numbers are from a sequence generator and you are merging two instances, then you have much bigger problems, since the two sequence generators in separate instances can easily produce duplicate numbers. The only way to prevent this is to add other "instance specific" characters to the sequence number so that even if the number is duplicated the rest of the field will be unique.
Example:

    select PlantLoc || InstanceName || AnyOtherUniqueThing || Seqgen.Nextval     into MySequenceVariable
    from dual;

or possibly lpad(Seqgen.nextval,10,'0'): this creates constant 10 character number with leading zero characters.
End result might be: LOSANGXYZMYKEY000001123 or: AABBCC000001

Robert Proffitt
Beckman Coulter
RTProffitt @ beckman.com
In article <37318361.4A5641EF_at_adr.it>,
  Kekko <dini.f_at_adr.it> wrote:
> Try to disable constraint on production table before inserting records.
> About the sequence I don't know the solution,
> while for the export you can export data without constraints (constraints
> = NO)
> Ciao,
> Francesco.
>
> Arthur Merar wrote:
>
> > Hello,
> >
> > I have two tables, our production table and a transaction table. I
> > want to insert the records from the transaction table into the
> > production table. The tables have the exact same layout.
> >
> > The problem is that the production has many constraints on it, and it
> > has a sequence as one of the fields. When I insert these records, I
> > get a unique constraint error on the sequence number.
> >
> > How can I insert these records have have the sequence work correctly?
> > Do I need to do something to the transaction table first???
> >
> > Also, when I export tables, how can I export the data without the
> > constraints, so when I import I do not get all the errors?
> >
> > Please send a copy to e-mail....
> >
> > Thanks,
> >
> > Arthur
> > amerar_at_unsu.com
> > amerar_at_ci.chi.il.us
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu May 06 1999 - 10:58:12 CDT