I am trying to APPEND a data extension with data views on a weekly basis with automation studio. It returns the error "Query failed during execution. Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object 'Complaints'." I thought DISTINCT will work but it only works when i OVERWRITE my data extension.
SELECT distinct contact.id as Id , contact.email as email , contact.FirstName as FirstName , contact.status__c as AtlasRelation , job.EmailName as EmailName , complaint.EventDate as ComplaintDate FROM contact_salesforce contact INNER JOIN _Complaint complaint ON complaint.SubscriberKey = contact.id LEFT JOIN _Job job on complaint.JobID = job.JobID
Can someone help me with this?