I am trying to query some specific email activities using SQL Activity and Data Views in SFMC. I am trying to fetch data from Subscriber, Sent and Job data view. I need EmailName, EmailAddress, SubscriberKey,EventDate and JobID field in the query results. Following is the the SQL Activity:
SELECT sub.SubscriberKey AS SubscriberKey, sub.EmailAddress AS EmailAddress, job.EmailName AS EmailName, sen.EventDate AS ActivityDate, sen.JobID AS JobID FROM ent._Subscribers AS sub INNER JOIN _Sent AS sen ON sub.SubscriberKey = sen.SubscriberKey INNER JOIN _Job AS job ON sen.JobID = job.JobID WHERE job.EmailName LIKE '%ABC%'
I am using SubScriberKey and JobId as a primary key in target data extension. UPDATE has been chosen as Data Action on the SQL Activity. After executing SQL query found that data view has some duplicate records i.e. same email has been sent to same subscriber as part of same send job so SubscriberKey and JobID is duplicate on some records. Because of this SQL query fails with "Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object" error.
I basically don't want the duplicate records in my query result. Is there a way to skip duplicate records in query results?