Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Copy records in database!
"Hans" <sorry_at_nospam.com> wrote in message news:<cfs5l3$3uj$1_at_green.tninet.se>...
> 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
Try the following:
insert into my_table
select * from my_table2 mt2 where ID='ABC' and not exists ( select 1 from my_table mt1 where mt1.primary_key = mt2.primary_key)
The above should not try to insert a duplicate primary key value, but test it.
HTH,
Pete's
Received on Tue Aug 17 2004 - 07:50:48 CDT