error message on sql: "Violation of PRIMARY KEY constraint. Cannot insert duplicate key."

Question

I'm getting this error for the SQL below:

Violation of PRIMARY KEY constraint. Cannot insert duplicate key.

Select DISTINCT 
SubscriberKey, 
Source
From (

  Select
  Email,
  et4ae5__HasOptedOutOfMobile__c as SubscribeSMS,
  HasOptedOutOfEmail as SubscribeEmail,
  Subscriber_ID__c as SubscriberKey,
   'lead' as Source
  from lead_Salesforce 

  Union ALL 
  
  Select 
  Email, 
  Unique_ID__c As SubscriberKey,
  'Contact' as Source,
  FirstName,
  LastName
  from Contact_Salesforce

) as tab
Where SubscriberKey IS NOT NULL
AND SubscriberKey != ''

Answer 1

This error is due to your query returning duplicate values for your primary key column. I assume your primary key column is SubscriberKey. When applying DISTINCT, you will get distinct values across both SubscriberKey and Source. Hence if one SubscriberKey is present on multiple sources, your query will fail. You can solve it by making both SubscriberKey and Source primary key.

Answer 2

I think your sub-queries should include the same columns to satisfy the UNION ALL syntax. You can use a partition to deduplicate and comply with your target DEs primary key.

I'd recommend selecting a date column (modified or created) in your sub-queries and using that value pick the row to return in your partition:

Select top 1 with ties
  x.SubscriberKey
, x.Source
from (

  Select
    l.Email
  , l.Subscriber_ID__c as SubscriberKey
  , l.et4ae5__HasOptedOutOfMobile__c as SubscribeSMS
  , 'Lead' as Source
  , l.HasOptedOutOfEmail as SubscribeEmail
  , null as FirstName
  , null as LastName
  , l.modifiedDate lastmodified /* or whatever is appropriate */
  from Lead_Salesforce l

  union all 
  
  Select 
    c.Email
  , c.Unique_ID__c As SubscriberKey
  , null as SubscriberSMS
  , 'Contact' as Source
  , c.FirstName
  , c.LastName
  , c.modifiedDate lastmodified /* or whatever is appropriate */
  from Contact_Salesforce c

) x
Where isnull(x.SubscriberKey,'') != ''
order by row_number() over (partition x.subscriberkey, x.source order by x.lastmodified desc)

Answer 3

SELECT l.Email AS Email ,l.et4ae5__HasOptedOutOfMobile__c AS SubscribeSMS ,l.HasOptedOutOfEmail AS SubscribeEmail ,NULL AS FirstName ,NULL AS LastName ,l.Subscriber_ID__c AS SubscriberKey ,'Lead' as Source ,NULL AS MobilePhone FROM Lead_Salesforce AS l WHERE ISNULL(l.Subscriber_ID__c, '') <> '' AND NOT EXISTS (SELECT 1 FROM Contact_Salesforce AS c WHERE c.Unique_ID__c = l.Subscriber_ID__c AND c.Unique_ID__c IS NOT NULL)

UNION

SELECT cs.Email AS Email ,NULL AS SubscribeSMS ,cs.HasoptedOutOfEmail AS SubscribeEmail ,cs.FirstName AS FirstName ,cs.LastName AS LastName ,cs.Unique_ID__c AS SubscriberKey ,'Contact' AS Source ,cs.MobilePhone AS MobilePhone FROM Contact_Salesforce AS cs WHERE ISNULL(cs.Unique_ID__c, '') <> ''