0

I run this SQL Query Activity

SELECT
a.Id,
a.Active__c,
a.Age__c,
a.Email,
a.FirstName,
a.Salutation,
a.PreferredLanguage__c,
a.HasOptedOutOfEmail,


FROM ent.Contact_Salesforce_43 a 
LEFT JOIN  ent.Channel__c_Salesforce_11 b ON a.Id=b.Contact__c 
LEFT JOIN  ent.Member__c_Salesforce_1 c ON a.Id=c.MainContact__c 
LEFT JOIN  ent.Brand__c_Salesforce_12 d ON a.Id=d.PrimaryContact__c

Target DE: Main_DE, Id is the Primary Key and Id relates to Subscribers on Subscriber Key.

in the Automation Studio and continue to have this error:

Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Main_DE'. The duplicate key value is (xxxxxxxxxxxxxxx).

Violation of PRIMARY KEY constraint. Cannot insert duplicate key.

saying that there is a duplicate key value for one record only even though:

  • I cleared all the data from the target Date Extension,
  • There are several other records in the Select DE,
  • See only one contact with this ID in the All Contacts list,
  • Have exactly the same activity running in another Business Unit correctly.

The SQL Query itself was validated correctly. I tried a range of solutions posted online but the error continues to be there. Is it possible that this is some technical problem on the Marketing Cloud end?

  • 2
    Please update your question to include the query text and the details of your target data extension. If you're getting a primary key violation, your query is returning duplicates by the primary key field values. – Adam Spriggs Aug 19 at 18:35
  • Updated my question above – jamietaylor Aug 19 at 19:12
  • I always recommend removing your primary key so the multiple records can be inserted then review your data – EazyE Aug 19 at 21:40
2

Likely the issue is coming from your left joins. If there are multiple possible joins across those tables, then it can create duplicate records since these will be recognized as different records. This article gives a good background on how/why.

To get around this, it might be a good idea to utilize ROW_NUMBER() to number the partitions you set where you can then grab just the records with a number of 1 (to remove duplicates).

Something like:

SELECT
x.Id,
x.Active__c,
x.Age__c,
x.Email,
x.FirstName,
x.Salutation,
x.PreferredLanguage__c,
x.HasOptedOutOfEmail
FROM (
    SELECT
    a.Id,
    a.Active__c,
    a.Age__c,
    a.Email,
    a.FirstName,
    a.Salutation,
    a.PreferredLanguage__c,
    a.HasOptedOutOfEmail,
    ROW_NUMBER() OVER(PARTITION BY a.ID ORDER BY a.ID) as rn
    FROM ent.Contact_Salesforce_43 a 
    LEFT JOIN  ent.Channel__c_Salesforce_11 b ON a.Id=b.Contact__c 
    LEFT JOIN  ent.Member__c_Salesforce_1 c ON a.Id=c.MainContact__c 
    LEFT JOIN  ent.Brand__c_Salesforce_12 d ON a.Id=d.PrimaryContact__c
) x
WHERE x.rn = 1

There are other solutions, including GROUP BY, Reconfiguring relationships/foreign keys, reconfiguring data structure of your DE, etc. I usually recommend ROW_NUMBER() due to simplicity.

One caveat with it is that if any of the duplicates have different data in same fields, then you could be potentially missing some relational data.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.