使用复合主键时避免插入重复项
使用复合主键时避免插入重复项
提问于 2020-01-15 18:07:07
我正在用SQL编写一个查询，将数据从Acces DB传输到新的SQL server管理数据库。这种传输只有一次，所以我不必担心代码是超级通用的。
我试图插入几个表中的数据(包括Acces DB中的表)，但得到一个关于复合主键上重复项的错误。
我的问题是，我不明白为什么我使用distinct不能防止这些重复。
我想要插入的表是这样构造的：
我尝试执行的代码是这样写的：
-- Insert into ComponentSupplier for [Supplier 1]
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 1 order no], a.[PDF Data 1 sheet link], a.[Supplier 1 LF], '1'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 1]
-- Insert into ComponentSupplier for [Supplier 2]
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 2 order no], a.[PDF Data 2 sheet link], a.[Supplier 2 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 2]
-- Insert into ComponentSupplier for [Supplier 3]
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 3 order no], a.[PDF Data 3 sheet link], a.[Supplier 3 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 3]
-- Insert into ComponentSupplier for [Supplier 4]
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 4 order no], a.[PDF Data 4 sheet link], a.[Supplier 4 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 4]
-- Insert into ComponentSupplier for [Supplier 5]
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 5 order no], a.[PDF Data 5 sheet link], a.[Supplier 5 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 5]
-- Insert into ComponentSupplier for [Supplier 6]
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 6 order no], a.[PDF Data 6 sheet link], a.[Supplier 6 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 6]回答 1
Stack Overflow用户
发布于 2020-01-15 18:11:23
distinct基于select中的所有列执行重复数据消除。
问题可能是因为您正在使用多个insert..selects插入记录，您可以尝试类似这样的操作，以确保正确地消除重复记录。
distinct将对每个select执行重复数据消除，union将跨select语句执行重复数据消除：
insert into CDB2020.dbo.ComponentSupplier
(
Supplier_ID,
BK_ID,
Part_ID,
Datasheet,
LF,
Preferred
)
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 1 order no], a.[PDF Data 1 sheet link], a.[Supplier 1 LF], '1'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 1]
union
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 2 order no], a.[PDF Data 2 sheet link], a.[Supplier 2 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 2]
union
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 3 order no], a.[PDF Data 3 sheet link], a.[Supplier 3 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 3]
union
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 4 order no], a.[PDF Data 4 sheet link], a.[Supplier 4 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 4]
union
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 5 order no], a.[PDF Data 5 sheet link], a.[Supplier 5 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 5]
union
select distinct s.Supplier_ID, c.BK_ID, a.[Supplier 6 order no], a.[PDF Data 6 sheet link], a.[Supplier 6 LF], '0'
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 6]您可以使用这样的查询来查找重复的记录(group by您的组合键中的所有字段)：
select s.Supplier_ID, c.BK_ID, count(*)
from AccessDataMigration_1.dbo.[B-K Data] as a
inner join CDB2020.dbo.Components as c on c.BK_ID = a.[B-K no]
inner join CDB2020.dbo.Suppliers as s on s.Name = a.[Supplier 6]
group by s.Supplier_ID, c.BK_ID
having count(*) > 1页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/59749122
复制相关文章
相似问题
腾讯云开发者
