I would like to insert data from a table which is in A server from the B server.
select count(*) from A.table
-- 100 rows affected
delete from A.table where customer_code = '100'
-- 10 rows affected
select count(*) from B.table
-- 200 rows affected
select count(*) from B.table where customer_code='100'
-- 20 rows affected
both the tables have identity(1,1) and primary_key
insert into A.table(customer_key,customer_code,custome_name)
from B.table where customer_code='100'
--Violation of PRIMARY KEY constraint . Cannot insert duplicate key in object 'A.table'.
I have already tried
SET IDENTITY_INSERT <> ON
DBCC CHECKIDENT(<>, RESEED,0)
The Primary Key Violation is telling you that the at least one of the values for
A.table that you are trying to insert from
B.Table is already in use for a different Customer record in
A (and assuming that you've already run your delete statement for this
This means it is already too late to consider trying to keep the surrogate identity column
customer_key in synch between the two tables A and B (as you say you are not are in a position to truncate
A and copy across from scratch from
B, if applicable). However, it seems that
customer_code does not provide unique identification (idempotence) of a customer either (since the delete removed 10 rows).
So in summary - if you don't need to establish any link other than by
customer_code, and potentially via
customer_name, you can copy the data into
A which will be assigned new identity
insert into A.table(customer_code,custome_name) select customer_code,customer_name from B.table where customer_code='100'
Otherwise, if you do need to uniquely identify rows between the tables, what you will need to do is add new storage for the link between the 2 tables. A quick and dirty way would be to add B's surrogate directly to A, like so:
ALTER TABLE A.table ADD customer_key_TableB INT NULL -- Or whatever the type of `customer_key` GO
Then insert and link the data like so (Again, with
IDENTITY INSERT for Table A still off):
insert into A.table(customer_code, customer_name, customer_key_TableB) select customer_code, customer_name, customer_key from B.table where customer_code='100'