Re: Copying rows

In article <3678CAC1.85823AF2_at_statoil.com>,   "Morten Bråten" <mobra_at_statoil.com> wrote:
> 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
>
>

I don't have my reference book here, but look at the data dictionary tables, for example user_tab_columns contains information about the columns in your tables.

With that information you can create a SELECT statement that generates a spool file that contains the INSERT statement needed to do this operation. Then you just execute the spooled file. The result of this is you write this meta-query once. If columns are added or removed, the SELECT statement picks up the change and does the right thing. Here's a start:

set heading off
set feedback off
spool genquery.sql
select 'select my_seq.nextval' from dual; select ', ' || column_name from user_tab_columns

    where table_name = 'MY_TABLE';
select ' from MY_TABLE ; ' from dual;
spool off
@genquery

The above example needs some work but has the key ideas. Let me know if you need help writing this. You can generate nearly any kind of SQL script you need this way.

It's a powerful technique.
Use it only for good, never evil 8^)

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own