Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Copy records in database!

Re: Copy records in database!

From: Pete's <empete2000_at_yahoo.com>
Date: 17 Aug 2004 05:50:48 -0700
Message-ID: <6724a51f.0408170450.396e58bc@posting.google.com>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US