I have a SQL query that is trying to pull three records and insert them into one. On table A, affiliation ID is the primary key, and a contact can have more than one affiliation. On table B, Contact ID is the primary key. So when my query runs, it may find a contact more than once, and try to insert it into Table B. Naturally, it fails. This is expected. What I CAN'T sort out, is that it only fails 4 times, and then runs fine the rest of the day, even though the duplicates remain. This is making no sense to me as the query just just keep failing all day. The query just ran and I checked and the duplicate values are still there, yet the query succeeded. We have noticed on some that the affiliation records are brand new the day they fail, but that doesn't help me much. Does anyone know if Marketing Cloud can resolve these types of errors on its own? It seems to be that may be what is happening.
The query producing the errors looks like this:
, '1' as workgroup_a
eda_affiliations_master with (nolock)
workforce_group = 'A'
Also, I know I can solve this with a simple group by, but what I'm really after is the odd behavior of it failing only sometimes.