0

I'm trying to combine two data extensions into a master data extension. We use email address as a primary key. I setup the query below:

select
[EmailAddress] AS [Email],[FirstName] AS [First Name],[LastName] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Address] AS [Address],[City] AS [City],[State] AS [State Province],[ZipCode] AS [Zip Code],[Country] AS [Country]
from Filter_CorporateCustomer
union
select
[Email] AS [Email],[First Name] AS [First Name],[Last Name] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Street Address] AS [Address],[City] AS [City],[State Province] AS [State Province],[Zip Postal Code] AS [Zip Code],[Country] AS [Country]
from E_Corporate

The query fails: Error: Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object.

I obviously have duplicate emails across the two data extension. Is there a best practice for SQL that I can add the data from the first but not the second if its a duplicate?

  • what do you have the Update Type set to? Also, are these duplicates identical or will these need to be unique entries? – Gortonington Nov 8 '17 at 18:04
  • Update type is set to "Update." They don't need to be unique entries, I'd just prefer to use the entry from the first data extension. – Ryan Kanine Nov 8 '17 at 18:29
2

Give this a go the left joining should be email and not emailaddress

select
[EmailAddress] AS [Email],[FirstName] AS [First Name],[LastName] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Address] AS [Address],[City] AS [City],[State] AS [State Province],[ZipCode] AS [Zip Code],[Country] AS [Country]
from Filter_CorporateCustomer f
LEFT JOIN E_Corporate e ON  f.[EmailAddress] = e.[Email] 
WHERE e.[Email] IS NULL
0

To avoid a primary violation Try this:

select
[EmailAddress] AS [Email],[FirstName] AS [First Name],[LastName] AS [Last Name],[Account] AS [Company Name],[Job Title] AS [Job Title],[Phone] AS [Phone],[Address] AS [Address],[City] AS [City],[State] AS [State Province],[ZipCode] AS [Zip Code],[Country] AS [Country]
from Filter_CorporateCustomer f
LEFT JOIN
E_Corporate e 

ON  f.[EmailAddress] = e.[EmailAddress] 

WHERE e.[EmailAddress] IS NULL

This will exclude contacts that are already present in the first DE.

  • The syntax keeps coming up invalid. Since I matched up the column names for the first data extension, wouldn't I have to do the same for the bottom to ensure that when they join together everything falls in the correct column? – Ryan Kanine Nov 8 '17 at 21:13
  • Can you please post the full error? I have looked through it and it seems fine. You dont need to going on all columns just one for this query. – Samuel Dare Nov 9 '17 at 16:34
  • I receive: The query syntax is invalid from is not a known data extension or system data view. You can only query existing data extensions or system data views. – Ryan Kanine Nov 9 '17 at 16:41
  • And if I get rid of "from" after Left Join then I'm getting invalid column name errors. – Ryan Kanine Nov 9 '17 at 16:41
  • Missed that , the second from should not be there – Samuel Dare Nov 9 '17 at 17:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.