Every day a file is dropped on an FTP which consists of people with their relationnumbers (relatienummer) and the products (documentnummer) that they have bought. However, the same person can buy multiple products. I have a journey that is only for people that buy 1 specific product in the file and I want to select these people and insert them into a Data Extension in which relation number is the identifier.
This is my query:
SELECT DISTINCT m.Relatienummer,
m.Aanmaakdatum,
m.Documentnummer
FROM Acquisitie_Campagne_Testrapporten_PROD m
WHERE Artikelcode = 'DOS006' AND m.Aanmaakdatum = Cast(Dateadd(dd, -1, Getdate())as date)
Query failed during execution.
Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate
key in object 'Acquisitie_Campagne_Main_Contacts_PROD'. The duplicate
key value is (0001423—).
Can someone help me with this error?
Best Answer
Generally speaking you'd use a T-SQL windowing function to de-duplicate the rows.
If your primary key in the target data extension is only
Relatienummer
, then you can partition by that field and order the duplicates byAanmaakdatum
. Therow_number()
numbers them in order. The where-clause in the outer query picks the first one for eachRelatienummer
.I have a list of fixes for the 4 types of SFMC query errors on my blog.