Identity insert few records without truncating to an existing table

Question

I would like to insert data from a table which is in A server from the B server.

ex:

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)
select 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)

I'm using SQL Server 2005.

StuartLC · Answer 1 · 2013-01-23T12:09:18.950

The Primary Key Violation is telling you that the at least one of the values for customer_key in 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 customer_code).

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 customer_key's:

(i.e. leaving IDENTITY_INSERT OFF)

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'

Something has to give here. You either need to delete all the data on table `A..Customer` and reinsert from scratch (in which case, you may as well drop the identity on `A..Customer`, since it is driven from `B`), or otherwise you need to admit that it is impossible to merge the data into `A` and retain `B`'s surrogate id with the same value as the key for `A` as well, unless you drop the PK on A and allow duplicates. — StuartLC, Jan 23 '13 at 12:32

Identity insert few records without truncating to an existing table

1 Answers1