使用NOT EXISTS插入#productType表出现重复且无法用MERGE如何解决

问题原因 #

• 重复插入的核心原因是SQL语句的原子执行特性：INSERT操作的查询、条件判断、数据写入三个步骤是基于同一个执行前快照完成的，not exists判断读取的是本次插入操作执行前的#productType表数据，此时表为空，所有18条来自#product的记录都满足判断条件，最终被全部写入。
• 你的#productType表仅对自增主键ProductTypeId做了唯一性限制，没有对Name字段加唯一约束，因此相同Name的记录可以重复写入。

适配事务场景的修改方案（无需使用MERGE语句） #

方案1：插入前对源数据去重 #

给SELECT语句增加DISTINCT关键字，直接保证待插入的TypeName本身无重复，配合not exists判断已存在的记录即可：

insert into #productType ([Name])
select DISTINCT [TypeName] from #product p
where not exists (SELECT 1 FROM #productType cpt
 WHERE cpt.[Name] = p.TypeName)

方案2：增加字段唯一约束兜底 #

如果业务层面要求Name字段全局唯一，可以直接在表结构中给Name加唯一约束，从根本上避免重复数据写入：

create table #productType (
    [ProductTypeId] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](30) NOT NULL UNIQUE, -- 新增唯一约束
    CONSTRAINT [PK_ProductType] PRIMARY KEY CLUSTERED 
    (
        [ProductTypeId] ASC
    )
);

方案3：分组后插入（适用于多字段去重场景） #

如果去重判断条件涉及多个字段，可以先对源数据分组后再执行插入判断：

insert into #productType ([Name])
select [TypeName] 
from #product p
where not exists (SELECT 1 FROM #productType cpt WHERE cpt.[Name] = p.TypeName)
group by [TypeName]

以上方案均兼容DbUp的事务执行逻辑，修改后#productType表仅会插入1条MyProduct记录，符合预期。

内容的提问来源于stack exchange，提问作者TugboatCaptain