sql-server - 插入不同值 SQL Server

Question

我正在尝试将唯一值从 View 插入表中。我有一张表如下:“fromView”在护照中没有唯一的约束

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

我的“toTable”具有相同的数据结构，但在护照列中有唯一的约束。

我的插入查询是这样的:

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)

但这给了我以下错误:

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

所以，我不知道如何将唯一值插入我的表中。提前致谢

Answer 1

您可以通过运行以下查询来获取具有多个条目的所有护照的列表:

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

然后您必须决定如何处理这些重复的行。运行以下查询以查看这些重复项的完整行:

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

假设您决定为每本护照插入最新行(这意味着 ID 将是最高的)，此查询将为您提供所需的数据。

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

您可以使用插入

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
)