0

I have an SQL query running in automation studio on a daily basis. It takes data from data extension A and puts it in data extension B. It functioned without any errors for 4 months, but it started to return an error since last week: 'Violation of PRIMARY KEY constraint. Cannot insert duplicate key'

Does anyone know what the issue could be? Thanks!

SELECT 
SubscriberKey, EmailAddress, [First Name], [Last Name], Mobile, [Date Of Birth], Gender, Nationality, [Customer ID], SubscribedToNewsletter, [Registration Date] as [Subscription Date]
FROM [Welcome Triggered Send] t1
WHERE SubscribedToNewsletter = 'TRUE'
AND NOT EXISTS (SELECT EmailAddress
                FROM [Master DE Newsletter] t2
                WHERE t2.EmailAddress = t1.EmailAddress)
2
  • Have you tried adding DISTINCT on top of the main query?
    – Niko
    Jul 4, 2021 at 19:56
  • 1
    The important parts to isolate this error are: The primary key of your target DE (I assume, subscriberKey) and the mode of insertion (add, (add & ) update, overwrite). Please add those to the question so we can help you better. Likely, your process doesn't account for someone coming into the target DE twice, and this case simply has not occurred for some time. Once it did, it broke as adding the same primary key twice is invalid. Jul 5, 2021 at 6:42

1 Answer 1

0

As Jobas Lambery has started, the error is happening as the same primary key is attempted to be added twice.

There are two ways to tix this:

  1. You can add another primary key to the target DE. This will mean that you are likely to have duplicate subscriberkey's in your resulting data (which may not be a good idea)

Or (Probably the better option)

  1. You can add a dedupe criteria to your SQL query. Assuming the Subscriberkey is your PK, please see the query below. We are finding all common subscriberkey's (PK) in your data, ordering them by [Registration Date] from most recent subscription to least recent and taking that most recent record.

I have also optimized your query by replacing the "NOT EXISTS" section with a LEFT JOIN. The system will processes this more efficiently than the "NOT EXISTS" section.

SELECT 
SubscriberKey, 
EmailAddress, 
[First Name], 
[Last Name], 
Mobile, 
[Date Of Birth], 
Gender, 
Nationality, 
[Customer ID], 
SubscribedToNewsletter, 
[Registration Date] as [Subscription Date]
FROM 
(   
    SELECT 
    t1.SubscriberKey, 
    t1.EmailAddress, 
    t1.[First Name], 
    t1.[Last Name], 
    t1.Mobile, 
    t1.[Date Of Birth], 
    t1.Gender, 
    t1.Nationality, 
    t1.[Customer ID], 
    t1.SubscribedToNewsletter, 
    t1.[Registration Date] as [Subscription Date],
    ROW_NUMBER() OVER (PARTITION BY t1.[Subscriberkey] ORDER BY t1.[Registration Date]  ASC) AS [RN] /*dedupe at Subscriberkey lvl*/ 

    FROM [Welcome Triggered Send] t1
    LEFT JOIN [Master DE Newsletter] t2
    ON t2.EmailAddress = t1.EmailAddress
    WHERE 
    SubscribedToNewsletter = 'TRUE'
    AND 
    t2.EmailAddress IS NULL 
) AS SQ
WHERE 
[RN] = 1

Read this info to better understand the SQL criteria to dedupe: https://www.sqlservercentral.com/articles/eliminating-duplicate-rows-using-the-partition-by-clause

I hope this helps

Take care, Weenus23

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.