Re: Copying rows

You could use a before_insert trigger on the table to whatever value is in the primary key column with the next sequence number, then your query would not need to specify column names. This has the downside of assuming that you always want duplicate row inserts to succeed, but nothing's perfect. <g>

Morten Bråten wrote in message <3678CAC1.85823AF2_at_statoil.com>...
>I have a table with 15-20 columns. The first column is the
>primary key, which I use a sequence to generate.
>
>I want to copy n number of rows from the table and append
>them to the same table. The copied rows must get a new
>sequence number, since the primary key definition doesn't
>allow duplicates.
>
>I can probably use something like
>INSERT INTO table (keyfield, field2, field3, field4,..., fieldn)
>VALUES (SELECT sequence.nextval, field2, field3, field4, ..., fieldn
>FROM table WHERE ...)
>
>But the problem is that my table structure is subject to
>frequent changes, and I would have to update the copy query/procedure
>each time
>I changed the table. I'm looking for a way to programmatically get the
>column names,
>and make a generic (PL/SQL) procedure that copies all fields (except the
>primary key, which is filled in by the sequence) to a new row.
>
>Can anybody help me with this?
>
>- Morten
>
>
Received on Thu Dec 17 1998 - 00:00:00 CST