Violation of PRIMARY KEY constraint
I have two databases CorpWear265_Restore_Test &Â CorpWear265_Restore_TestAl
The second has the same structure as the first.
I am trying to copy the records from dBase: CorpWear265_Restore_Test table: ProductVariantAttributeVal
into dBase: CorpWear265_Restore_TestAl
The error I get is as follows:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__ProductVariantAt__07C
The statement has been terminated.
The complete sql code is listed below:
Thanks in advance for any help given.
The second has the same structure as the first.
I am trying to copy the records from dBase: CorpWear265_Restore_Test table: ProductVariantAttributeVal
into dBase: CorpWear265_Restore_TestAl
The error I get is as follows:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK__ProductVariantAt__07C
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
As you are trying to copy ALL records then you need to make sure that the CorpWear265_Restore_TestAl
Alternatively only copy records that do not exist in the destination from the source.
Alternatively only copy records that do not exist in the destination from the source.
ASKER
HI, Thanks for your suggestion.
I have now been able to get all records transferred.
The code now used is below:
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.IDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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;