Re: Copying rows

Thank you everyone who helped me with this... it seems thatthere are at least two ways to do this:
1) create the query by using the data in user_tab_columns, or 2) use a trigger to insert the sequence value

Option 2) seems to require less maintainance. Any caveats I should be aware of using this method?

Again, thanks!

• Morten

> 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 Mon Dec 21 1998 - 00:00:00 CET