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?