SQL Server生成无冲突插入值并跨表检查的代码实现咨询

嘿，这个需求我之前也帮人处理过，其实有两种靠谱的实现方式，咱们可以根据场景来选：

方案一：用WHILE循环（逻辑直观，适合小数据量） #

这种方式思路特别直接：先拿到表A目标列的最大值加1，然后循环检查表B里有没有这个值，有就自增，直到找到一个不存在的，再用这个值执行INSERT。

BEGIN TRANSACTION; -- 开启事务，避免并发场景下生成重复值

DECLARE @newVal INT;

-- 获取表A目标列的最大值加1，处理表A为空的情况，默认从1开始
SELECT @newVal = ISNULL(MAX(目标列名), 0) + 1 FROM 表A WITH (UPDLOCK, HOLDLOCK);
-- 加UPDLOCK和HOLDLOCK是为了锁定表A的最大值，防止多会话同时取到相同的初始值

-- 循环检查表B是否存在当前值，存在就自增
WHILE EXISTS(SELECT 1 FROM 表B WHERE 目标列名 = @newVal)
BEGIN
    SET @newVal = @newVal + 1;
END

-- 执行INSERT语句，把生成的不重复值插入目标表
INSERT INTO 目标表(目标列名, 其他列...)
VALUES (@newVal, '其他示例值...');

COMMIT TRANSACTION;

方案二：用CTE生成序列（高效，适合大数据量） #

如果表B里的数据量很大，循环可能会拖慢速度，这时候可以用CTE生成一批连续的数值，直接找到第一个不在表B里的，效率会高很多。

BEGIN TRANSACTION;

DECLARE @startVal INT;
SELECT @startVal = ISNULL(MAX(目标列名), 0) + 1 FROM 表A WITH (UPDLOCK, HOLDLOCK);

-- 用CTE生成从@startVal开始的连续数值（这里生成1000个，可根据实际需求调整）
WITH GeneratedNumbers AS (
    SELECT @startVal AS Num
    UNION ALL
    SELECT Num + 1 FROM GeneratedNumbers
    WHERE Num + 1 <= @startVal + 999 -- 限制生成的候选值数量，避免无限递归
)
-- 筛选出第一个不在表B里的数值
SELECT @startVal = Num 
FROM GeneratedNumbers
WHERE NOT EXISTS(SELECT 1 FROM 表B WHERE 目标列名 = Num)
OPTION (MAXRECURSION 1000); -- 递归次数要和生成的候选数匹配

-- 执行INSERT
INSERT INTO 目标表(目标列名, 其他列...)
VALUES (@startVal, '其他示例值...');

COMMIT TRANSACTION;

重要注意事项 #

• 并发冲突：一定要加事务和表锁（UPDLOCK+HOLDLOCK），不然多个会话同时执行时，很可能会生成重复的值。
• 空表处理：用ISNULL处理表A为空的情况，确保初始值从你需要的起点开始（示例里是1）。
• 递归限制：方案二里的MAXRECURSION要和你生成的候选数匹配，默认递归次数是100，如果需要生成更多，得手动调整这个值。

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