Hi,
I use the following script to merge data from one table into another. This works fine 99% of the time. The issue I am having is that on occasion the data in in the source table pc_wiredorder holds multiple records with the same customerid, which is merged into the destination table wce_contact's uniqueid column which is the primary key field and therefore can't hold duplicate id's spo the script fails.
Error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'pk_wce_contact_uid'. Cannot insert duplicate key in object 'dbo.wce_contact'.
The statement has been terminated.
Example merge data below that causes the issues, this customerid is in the source table twice and I need to merge/insert into the wce_contact table. There could be more rows with the same customerid. Ideally I need the script to see the duplication, and handle one row at a time. Then bring the 2nd/3rd row in. The script is scheduled to run every 5 mins so the 2nd row could be handled then or maybe I could use this script and put an if clause in or something?
Any guidance would be really helpful. Thanks.
id ------------ CustomerId ------------ AddedToWired
28903 ------------ 42936 ------------ 0
28904 ------------ 42936 ------------ 0
I use the following script to merge data from one table into another. This works fine 99% of the time. The issue I am having is that on occasion the data in in the source table pc_wiredorder holds multiple records with the same customerid, which is merged into the destination table wce_contact's uniqueid column which is the primary key field and therefore can't hold duplicate id's spo the script fails.
Error:
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'pk_wce_contact_uid'. Cannot insert duplicate key in object 'dbo.wce_contact'.
The statement has been terminated.
Example merge data below that causes the issues, this customerid is in the source table twice and I need to merge/insert into the wce_contact table. There could be more rows with the same customerid. Ideally I need the script to see the duplication, and handle one row at a time. Then bring the 2nd/3rd row in. The script is scheduled to run every 5 mins so the 2nd row could be handled then or maybe I could use this script and put an if clause in or something?
Any guidance would be really helpful. Thanks.
id ------------ CustomerId ------------ AddedToWired
28903 ------------ 42936 ------------ 0
28904 ------------ 42936 ------------ 0
Code:
MERGE INTO wce_contact AS Target
using (select * from pc_wiredorder where addedtowired = '0') AS Source ON Target.emailaddress = Source.BillingEmail
WHEN NOT MATCHED THEN
INSERT ([uniqueid], [createuser], [edituser], [recordmanager], [createtime], [edittime], [COMPANY], [contact], [FirstName], [lastname], [emailaddress], [Phone], [Address1], [Address2], [Address3], [City],
[County], [Postalcode], [Customer_Type], [customerpipeline], [account_manager], [Referred_By], record_type, OP_Gone_Ahead)
VALUES (source.[customerid], 'ykn3t293i7ckyat7', 'ykn3t293i7ckyat7', 'ykn3t293i7ckyat7', getdate(), getdate(), source.[FirstName]+' '+ source.[Surname], source.[FirstName]+ ' ' + source.[Surname], source.[FirstName], source.[Surname], source.[BillingEmail], source.[Phone],
source.[AddressLine1], source.[AddressLine2], source.[AddressLine3], source.[TownCity], source.[County], source.[Postcode],
source.[CustomerType], source.[CustomerPipeline], source.[AccountManager], 'Web Integration', 'Individual', 'Confirmed');