January 12, 2003 at 9:18 am
I have a table inside database1, that is being update by client software, I need to insert the 'new' data from this table into a similiar table in another database after the 'new' data is inserted. Orginally, I used a insert query to do the work, but now if I do this I get a Primary Key violation and the statement terminates. How do I modify the following statement to handle existing Primary Keys?
use db1
INSERT INTO table1
(layer_id, table_name)
SELECT layer_id, table_name
FROM db2.owner.table2
January 13, 2003 at 5:00 am
What is it you want to achieve? If the primary key exists do you want to not do anything, insert the record with a different primary key, delete the orignal record first then insert or somthing else?
Regards,
Andy Jones
.
January 13, 2003 at 7:33 am
I want to add additional data where the primary key doesn't exist. For example, I have PK's 1-100, I have new records 101-150. I want to add those 50 new records.
January 13, 2003 at 8:26 am
Hi, you could use: -
use db1
insert table1
(
layer_id
,table_name
)
select
layer_id
, table_name
from db2.owner.table2
where layer_id not in
(
select
layer_id
from
table1
)
Alternatively you could use a 'not exists' instead of a 'not in'
Regards,
Andy Jones
.
January 13, 2003 at 8:36 am
thank you. I wasn't how my sql was supposed to be ordered, it worked, so I now I have it for future reference,
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply