Copy records in database!

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 Received on Tue Aug 17 2004 - 00:37:07 CDT