Re: Copy records in database!

Hans wrote:

> Hi!
>
> I want to duplicate records in a table and a SQL statement like this will
> almost do the task.
> INSERT INTO my_table SELECT * FROM my_table WHERE ID='ABC'
>
> Since the primary key is not generated by Oracle this will cause a duplicate
> key error. Do you have any clever ideas how to solve this? Of course I can
> name the columns instead of selecting all columns and create a new key but
> it is a bit cumbersome (the tables are not static so the field names must be
> fetched from the database and SQL statement must be generated dynamically).
> I guess this is still the way to go but there will be a bit more work to
> generate the SQL-statements.
>
> I came up with one alternative where I put the records I want to copy in a
> temp table and then update the key field and then insert the record back
> into the source table. This is done in only a few lines of code but I'm not
> sure this is a good solution.
>
> I have to support Oracle 8.1.7 and higher (and also SQL-server7 and higher
> if possible). The client is a VB6 application using ADO to connect to the
> database (right now we use Oracle ODBC driver but we will migrate to
> Oracle's OLEDB provider soon).
>
> Please give me your opinion how to solve this pretty easy task.
>
> Regards
> /Hans

You have only one choice other than to name the columns and that choice is to put a BEFORE-INSERT trigger on the table that creates the primary key's on-the-fly for all records no matter how they go in.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)