Solved

Violation of PRIMARY KEY constraint

Posted on 2014-09-11
5
1,348 Views
Last Modified: 2014-09-11
I have two databases CorpWear265_Restore_Test & CorpWear265_Restore_TestAlt
The second has the same structure as the first.
I am trying to copy the records from dBase: CorpWear265_Restore_Test table: ProductVariantAttributeValue
into dBase: CorpWear265_Restore_TestAlt table: ProductVariantAttributeValue
The error I get is as follows:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__ProductVariantAt__07C12930'. Cannot insert duplicate key in object 'dbo.ProductVariantAttributeValue'. The duplicate key value is (1).
The statement has been terminated.
The complete sql code is listed below:
Thanks in advance for any help given.
SET IDENTITY_INSERT CorpWear265_Restore_TestAlt.dbo.ProductVariantAttributeValue ON
INSERT INTO CorpWear265_Restore_TestAlt.dbo.ProductVariantAttributeValue (Id,ProductVariantAttributeId,Name,PriceAdjustment,WeightAdjustment,IsPreSelected,DisplayOrder)
select  Id,ProductVariantAttributeId,Name,PriceAdjustment,WeightAdjustment,IsPreSelected,DisplayOrder
FROM CorpWear265_Restore_Test.dbo.ProductVariantAttributeValue

Open in new window

0
Comment
Question by:homeshopper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40316855
this means exactly what the error says: you are trying to insert (at least) one new record which comes with the same primary key value as an existing row, and/or with several rows in the SELECT having the same value for that key field.

possible solutions:
a1) do NOT try to copy those rows
a2) do an UPDATE instead of INSERT for the rows that have already the PK value set
b) copy them with other values for the primary key values
c) in your case, as it's the IDENTITY field: copy them without the key field (so not using the identity_insert stuff)

your turn, you have to choose;
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 40316862
As you are trying to copy ALL records then you need to make sure that the CorpWear265_Restore_TestAlt.dbo.ProductVariantAttributeValue table is empty before you run that code.
Alternatively only copy records that do not exist in the destination from the source.
0
 

Author Comment

by:homeshopper
ID: 40316966
HI, Thanks for your suggestion.
I have now been able to get all records transferred.
The code now used is below:
/*   
update table1
set col2 = T2.col2
from DataBaseName.dbo.table1 as T2
where table1.ID = T2.ID and
table1.col2 is null
*/
update CorpWear265_Restore_TestAlt.dbo.ProductVariantAttributeValue
set ProductVariantAttributeId = T2.ProductVariantAttributeId
  , Name = T2.Name
  , PriceAdjustment  = T2.PriceAdjustment
  , WeightAdjustment = T2.WeightAdjustment
  , IsPreSelected = T2.IsPreSelected
  , DisplayOrder = T2.DisplayOrder
  
from CorpWear265_Restore_Test.dbo.ProductVariantAttributeValue as T2
where ProductVariantAttributeValue.ID = T2.ID

Open in new window

0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 500 total points
ID: 40317149
How many records are there in total?
Your code does not insert records that are not already in the second table you still need to do the insert where the record is not in Table 2
0
 

Author Comment

by:homeshopper
ID: 40317200
Thankyou for your suggestion, what you say is correct.
In this case, I made a copy of an existing database and ran a very long database update script.
154 records of 10337 in the Name field had changed since the original script was written
by another developer. It transferred all 10337, but only 10 of the 154 altered records appeared.

The new short code updates all 154 records in the Name field to there correct values.
Thanks again for your suggestions
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Amazon RDS migrate to SQL Server 3 34
Using this function 4 42
Linked Server - SP with Param to VIew 7 24
Need to create and populate a column map table 5 21
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question