Insert distinct values SQL Server

Question

I'm trying to insert unique values into a table from a View. I have a table as below: the "fromView" has no unique constraint in passport

id  | passport | name | surname | address 
1      44543     John    Smith     xxxxx
2      10001     Mike    Thomps    avasfa
3      10001     Mike    Thomps    avasfa
4      10001     Mike    Thomps    avasfa
5      14221     Robert  Martinez  lkjij3

my "toTable" has the same data structure but with a unique constraint in passport column.

my insert query is this:

INSERT into toTable (id, passport, name, surname, address) 
SELECT (id, passport, name, surname, address) 
FROM fromView a 
WHERE passport IS NOT NULL AND NOT EXISTS (SELECT * 
                                           FROM toTable b
                                           WHERE b.passport = a.passport)

but this give me the error below:

Cannot insert duplicate key row in object 'toTable' with unique index 'toTable_Passport_Unique'.

So, I Don't know how to insert unique values into my table. Thanks in advance

Answer 1

You can get a list of all passports with multiple entries by running this query:

Select Passport, Count (*) NumEntries
From  fromTable
Group by Passport
Having Count (*) > 1

Then you have to decide what to do with these duplicate rows. Run the following query to see the full row for these duplicates:

Select *
From  fromTable
Where Passport In
(
    Select Passport, Count (*) NumEntries
    From  fromTable
    Group by Passport
    Having Count (*) > 1
)
Order by Passport

Let's say you decide to go with the newest row inserted for each passport (meaning the Id would be the highest), this query will give you the data you need.

Select T1.*
From  fromTable T1
Where Id In
(
    Select Max (Id) Id
    From  fromTable
    Group by Passport
)

You can insert using

INSERT into toTable (id, passport, name, surname, address) 
Select T1.*
From  fromTable T1
Where Id In
(
    Select Max (Id) Id
    From  fromTable
    Group by Passport
)