SQL Server中确保主键voertuig_nummer不重复的插入方案咨询

方法1：改用IDENTITY自增列（最稳妥） #

如果允许修改表结构，直接将voertuig_nummer设为自增主键是最优方案，SQL Server原生支持，彻底避免重复问题，还能完美处理并发插入场景。

修改表结构语句：

ALTER TABLE voertuig 
ALTER COLUMN voertuig_nummer INT IDENTITY(1,1) PRIMARY KEY;

简化后的插入语句：

INSERT INTO voertuig (kenteken)
SELECT te.Kenteken
FROM #TempExcelData te
WHERE NOT EXISTS (
    SELECT 1 FROM voertuig v WHERE v.kenteken = te.Kenteken
);

方法2：使用SEQUENCE对象 #

如果无法修改表结构为IDENTITY，可以创建独立的序列对象生成主键值，比手动计算MAX值更可靠，适配并发场景。

先创建序列：

CREATE SEQUENCE voertuig_seq
    AS INT
    START WITH (SELECT ISNULL(MAX(voertuig_nummer), 0) + 1 FROM voertuig)
    INCREMENT BY 1;

插入语句改为：

INSERT INTO voertuig (voertuig_nummer, kenteken)
SELECT 
    NEXT VALUE FOR voertuig_seq AS voertuig_nummer,
    te.Kenteken
FROM #TempExcelData te
WHERE NOT EXISTS (
    SELECT 1 FROM voertuig v WHERE v.kenteken = te.Kenteken
);

方法3：保留原逻辑的冲突兜底方案 #

如果受限于业务规则必须保留手动生成逻辑，可通过二次校验调整重复编号，同时配合事务锁避免并发冲突：

BEGIN TRANSACTION;

-- 锁定voertuig表，防止其他会话修改MAX值
SELECT MAX(voertuig_nummer) FROM voertuig WITH (UPDLOCK, HOLDLOCK);

WITH CandidateData AS (
    SELECT 
        ISNULL((SELECT MAX(v.voertuig_nummer) FROM voertuig v), 0) + ROW_NUMBER() OVER (ORDER BY te.Kenteken) AS candidate_id,
        te.Kenteken
    FROM #TempExcelData te
    WHERE NOT EXISTS (
        SELECT 1 FROM voertuig v WHERE v.kenteken = te.Kenteken
    )
),
AdjustedData AS (
    SELECT 
        CASE 
            WHEN EXISTS (SELECT 1 FROM voertuig v WHERE v.voertuig_nummer = cd.candidate_id)
            THEN (SELECT MAX(v.voertuig_nummer) FROM voertuig v) + ROW_NUMBER() OVER (ORDER BY cd.candidate_id)
            ELSE cd.candidate_id
        END AS voertuig_nummer,
        cd.Kenteken
    FROM CandidateData cd
)
INSERT INTO voertuig (voertuig_nummer, kenteken)
SELECT voertuig_nummer, Kenteken
FROM AdjustedData;

COMMIT TRANSACTION;

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