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

What DBMS are you using?

Raj More
– Raj More

2011-09-29 13:15:02 +00:00
Commented Sep 29, 2011 at 13:15 — Raj More
– Raj More, Commented Sep 29, 2011 at 13:15

Raj More · Accepted Answer · 2011-09-29 13:16:10Z

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
)

Mikael Eriksson · Accepted Answer · 2011-09-29 13:11:45Z

2

insert into toTable (id, passport, name, surname, address)
select id, passport, name, surname, address
from (
      select *,
             row_number() over(partition by passport order by id) as rn
      from fromTable
     ) as T
where rn = 1

answered Sep 29, 2011 at 13:11

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

2 Comments

grteibo Over a year ago

i really doesnt know about row_numer() and partition, but this is working, thanks!

Mikael Eriksson Over a year ago

@grteibo - Read more about it here. msdn.microsoft.com/en-us/library/ms186734.aspx

Widor · Accepted Answer · 2011-09-29 13:11:18Z

0

If we can assume that for the same passport, we will have the same name, surname, and address, and we only want the most recent (highest) id then try,

INSERT INTO toTable (id, passport, name, surname, address)
SELECT max(id), passport, name, surname, address
FROM fromTable
--optional WHERE clause in case there's already data in toTable:
WHERE passport NOT IN (SELECT to.passport from toTable [to])
GROUP BY passport, name, surname, address

answered Sep 29, 2011 at 13:11

Widor

13.3k7 gold badges47 silver badges67 bronze badges

3 Comments

Raj More Over a year ago

@Widow - that's a bad assumption

Widor Over a year ago

I wouldn't say it's that bad - we are dealing with passports, after all, and the example table includes the same assumption.

Raj More Over a year ago

People change addresses all the time, and sometimes names. I would not want to make that assumption.

Mykolas Simutis · Accepted Answer · 2011-09-29 13:36:54Z

0

insert into toTable (id, name, surname, addr, passport)
  select
    testA1.id, testA1.name, testA1.surname, testA1.addr, testA1.passport 
  from 
    fromTable as testA1 right join (select min(id) AS distinctID, passport from fromTable group by passport) as testA2 on 
    testA2.distinctID = testA1.id

answered Sep 29, 2011 at 13:36

Mykolas Simutis

2121 silver badge11 bronze badges

Collectives™ on Stack Overflow

Insert distinct values SQL Server

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related