April 9, 2021 at 9:08 pm
I'm hoping someone has seen this before because I've been doing this for years and never had this problem.
When trying to add new records to synchronize a table I keep getting a violation of the primary key constraint. The primary key is on ContactID and using the query below I cannot understand how there can be a violation of the primary key:
insert into DBDest.dbo.Contacts
( ContactID, CompanyID, EmailAddress, FirstName, Phone, LastModifiedDate)
select src.contactid, src.companyID, src.emailaddress, src.firstname, src.incomingCoordind,
src.LastName, src.OutgoingCoordInd, src.Phone, src.LastModifiedDate
from DBSource.dbo.Contacts src
where src.ContactID not in (select contactid from DBDest.dbo.Contacts)
April 9, 2021 at 9:14 pm
The source table, DBSource.dbo.Contacts, must have duplicate entries for the same ContactID.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 9, 2021 at 9:14 pm
is the column DBSource.dbo.Contacts.ContactID unique? If it is NOT unique on the source, you could have problems putting it into the destination where it expects it to be unique.
Heh, Scott beat me to answering this one.
What you could do is something like:
SELECT COUNT([ContactID]),count(DISTINCT [ContactID])
FROM DBSource.dbo.Contacts
IF those number match, then Scott and I are incorrect in our guess with this. If they DON'T match, then you have some duplicate ContractID's in your table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply