Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Merge Into Help 1

Status
Not open for further replies.

craigward

Programmer
Nov 13, 2007
230
GB
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

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');
 
The data seems to use customerid as foreign key and you try to make contacts from it. Merge isn't working that way. Just pick out the columns you want to use from pc_wiredorder grouped by customerid.

Bye, Olaf.

Olaf Doschke Software Engineering
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top