当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server 2005 - Identity insert few records without truncating to an existing table

问题描述:

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)

Please suggest.

网友答案:

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'
分享给朋友:
您可能感兴趣的文章:
随机阅读: