Re: Copy records in database!

> 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

Hi Pete and thankx for you answer!

I'm not sure if this is what I really want. Will this query ever insert any records? Because I will copy a record and paste the record in the same table so the primary key will always exist (or maybe I have missunderstood the your query). The question is if there is any clever way to write a SQL statement to do this or if I have to name the columns so I can omit fetching the already existing primary_key value and craete a new one like

Insert into my_table (primary_key, field1, field2) select 'new_key', field1, field2 from my_table where field1='ABC'

In my case the tables are much larger and the name of the tables/fields are not static so the insertstatement must in this case be created on the fly, which maybe is not that hard to do but I was hoping that there would be a smarter way of doing this.

Regards
/Hans Received on Tue Aug 17 2004 - 08:52:40 CDT