如何防止向SQL Server表中插入多条重复的受邀人业务数据

原有逻辑问题梳理 #

• 全局校验逻辑错误：外层IF NOT EXISTS是整体校验，只要待插入列表中有一条重复，就会直接跳过全部数据插入，无法实现逐行去重
• 子查询语法错误：当拆分后@temp表存在多条记录时，判断条件里的单行子查询会返回多行，直接触发SQL运行报错
• 字段名匹配错误：校验逻辑中使用了不存在的字段名InviteeMobileNumber，和test表实际定义的inviteephoneno不匹配，导致校验逻辑完全失效

修正后完整代码 #

declare @inviteename VARCHAR(500) = 'aa,bb' ,@inviteephoneno VARCHAR(500) = '123,45',@Code varchar(10)='123', @CustomerID VARCHAR(50) = 10  

DECLARE @tbl TABLE (
inviteename VARCHAR(100), 
inviteephoneno VARCHAR(100)
);

INSERT INTO @tbl ( inviteename, inviteephoneno) select  @inviteename, @inviteephoneno;

DECLARE @temp TABLE (
inviteename VARCHAR(100), 
inviteephoneno VARCHAR(100),
CustomerID VARCHAR(100)
);

;WITH cte1 AS
(
    SELECT  value AS inviteename , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
    FROM @tbl CROSS APPLY STRING_SPLIT(inviteename, ',')
), cte2 AS
(
    SELECT  value AS inviteephoneno , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
    FROM @tbl CROSS APPLY STRING_SPLIT(inviteephoneno, ',')
)

INSERT INTO @temp ( inviteename, inviteephoneno,CustomerID)
SELECT cte1.inviteename, cte2.inviteephoneno,@CustomerID   FROM cte1 INNER JOIN cte2 ON cte2.seq = cte1.seq; 

-- 核心修改：逐行校验去重插入
INSERT dbo.test (InviteeName ,inviteephoneno ) 
SELECT a.inviteename, a.inviteephoneno  
FROM @temp a 
WHERE NOT EXISTS (
    SELECT 1 FROM test b 
    WHERE b.inviteename = a.inviteename 
    AND b.inviteephoneno = a.inviteephoneno
)

修正说明 #

• 移除了外层全局判断，改为在插入的查询语句中通过NOT EXISTS逐行比对已有数据，仅插入库中不存在的记录
• 修正了字段名不匹配的问题，和test表结构保持一致
• 符合场景需求：如果已有aa/123的记录，只会插入bb/45的新数据，不会重复插入已有记录，也不会漏插新记录

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