Re: Copy records in database!

From: Mark Bole <makbo_at_pacbell.net>
Date: Tue, 17 Aug 2004 14:51:23 GMT
Message-ID: <LXoUc.1012$SR4.725@newssvr14.news.prodigy.com>

Daniel Morgan wrote:

> 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? [...]
>>
>> 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).
>>

[...]>

> 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.
> 

Since you want the solution to be portable to non-Oracle databases and driven from the client side, I think you've already found your own "clever" solution, which is the temp table approach. Assuming you have the extra storage and can catch and handle any unexpected errors in the "transaction", this solution is simple and generic (in fact, it's so portable I use the same approach successfully in a legacy R:Base database).

--Mark Bole Received on Tue Aug 17 2004 - 09:51:23 CDT