Link to home
Create AccountLog in
Avatar of CAMPzxzxDeathzxzx
CAMPzxzxDeathzxzx

asked on

How can I import unique records based on email

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

Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

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.

where
      i.IndividualID in (
            select
                  max( ii.IndividualID ) as latestIndividualID
            from dbo.Individuals ii
            group by
                  ii.Email
            )
;

Alternatively, a CreatedDateTime or a LastmodifiedDateTime on the Individuals table could be used to make sure the last record for this email was selected.

HTH
  David
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of CAMPzxzxDeathzxzx
CAMPzxzxDeathzxzx

ASKER

Thanks