Automation Studio - Violation of primary key constraint, Cannot insert duplicate key

Question

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)

Answer 1

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)

2. 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