How can I make this insert statement ignore a repeated email? The [Individuals] table may have multiple entries with the same email address - I only want a single record to be inserted for each email.
INSERT INTO [dbo].[PeopleDetails](Email, FirstName,LastName,Address1,Address2,City,Zip,PhoneNumber,StateID,CountryID,CommPrefTypeID, GalacticID, AccountApproved)
SELECT i.[Email], i.[First_Name], i.[Last_Name], i.[Address_1], i.[Address_2], i.[City], i.[Zip_Code], i.[Phone], s.StateID, c.CountryID, @CommPrefTypeID, @GalacticID, @AccountApproved
FROM [dbo].[Individuals] i
JOIN [dbo].[State] s ON i.[state] = s.[StateCode]
JOIN [dbo].[Country] c ON i.[Country] = c.[CountryName]
Open in new window
How will you break the tie between emails? That is, is there another record you'd use or an ID on the individual?
Assuming that there is an IndividualID in the Individuals table, something like this where clause might do the trick.
i.IndividualID in (
max( ii.IndividualID ) as latestIndividualID
from dbo.Individuals ii
Alternatively, a CreatedDateTime or a LastmodifiedDateTime on the Individuals table could be used to make sure the last record for this email was selected.