使用Cursor插入无重复随机数至表：添加条件与批量执行咨询

一、怎么给插入操作加条件？ #

1. 核心需求：避免重复组合 #

要保证生成的10个数字组合不重复，最直接的办法是插入前先查目标表有没有这个组合，用IF NOT EXISTS就能实现：

WHILE @@FETCH_STATUS = 0 
BEGIN 
    -- 检查当前组合是否已存在
    IF NOT EXISTS (
        SELECT 1 FROM GenNumbers 
        WHERE Number1 = @iNumber1 
          AND Number2 = @iNumber2 
          AND Number3 = @iNumber3 
          AND Number4 = @iNumber4 
          AND Number5 = @iNumber5 
          AND Number6 = @iNumber6 
          AND Number7 = @iNumber7 
          AND Number8 = @iNumber8 
          AND Number9 = @iNumber9 
          AND Number10 = @iNumber10
    )
    BEGIN
        INSERT INTO GenNumbers (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8, Number9, Number10) 
        VALUES (@iNumber1, @iNumber2, @iNumber3, @iNumber4, @iNumber5, @iNumber6, @iNumber7, @iNumber8, @iNumber9, @iNumber10 ) 
    END
    FETCH NEXT FROM GenRandNum INTO @iNumber1, @iNumber2, @iNumber3, @iNumber4, @iNumber5, @iNumber6, @iNumber7, @iNumber8, @iNumber9, @iNumber10
END 

2. 加自定义条件 #

如果还要加其他规则（比如数字总和大于250、至少一个奇数），直接在IF里追加判断就行：

IF NOT EXISTS (...) 
   AND (@iNumber1 + @iNumber2 + ... + @iNumber10) > 250 
   AND (@iNumber1 % 2 = 1 OR @iNumber2 % 2 = 1) -- 至少一个奇数
BEGIN
    INSERT ...
END

二、要不要用存储过程实现自动多次运行？ #

必须用，而且这是批量生成百万级数据的最优选择，理由：

1. 把生成逻辑封装成存储过程后，只需调用一次就能自动循环生成数据，不用手动反复跑脚本。
2. 可以加参数控制生成数量（比如指定生成100万条），灵活度拉满。
3. 存储过程执行效率比手动跑脚本稳定，适合大规模数据生成。

优化后的存储过程（替换低效游标） #

注意：你原来的游标方式效率极低，百万级数据会跑很久，建议用集合式方法批量生成，效率提升N倍：

CREATE PROCEDURE GenerateRandomNumbers
    @TargetCount INT -- 要生成的目标数据条数
AS
BEGIN
    SET NOCOUNT ON;

    -- 临时表存生成的随机组合，主键自动去重
    CREATE TABLE #TempNumbers (
        Number1 INT, Number2 INT, Number3 INT, Number4 INT, Number5 INT,
        Number6 INT, Number7 INT, Number8 INT, Number9 INT, Number10 INT,
        PRIMARY KEY (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8, Number9, Number10)
    )

    -- 循环生成直到达到目标数量
    WHILE (SELECT COUNT(*) FROM #TempNumbers) < @TargetCount
    BEGIN
        -- 一次生成1000条（可根据服务器性能调整批量大小）
        INSERT INTO #TempNumbers (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8, Number9, Number10)
        SELECT TOP 1000
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1,
            ABS(CAST(NEWID() AS binary(6)) %50) + 1
        FROM sys.all_columns c1
        CROSS JOIN sys.all_columns c2
        -- 排除已在临时表中的组合
        WHERE NOT EXISTS (
            SELECT 1 FROM #TempNumbers tn
            WHERE tn.Number1 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number2 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number3 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number4 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number5 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number6 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number7 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number8 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number9 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
              AND tn.Number10 = ABS(CAST(NEWID() AS binary(6)) %50) + 1
        )
    END

    -- 把临时表数据批量插入目标表
    INSERT INTO GenNumbers (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8, Number9, Number10)
    SELECT TOP (@TargetCount) * FROM #TempNumbers

    DROP TABLE #TempNumbers
END

调用方式（生成百万级数据） #

-- 生成100万条无重复随机组合数据
EXEC GenerateRandomNumbers @TargetCount = 1000000

额外优化建议 #

• 给GenNumbers表的10个数字字段建联合唯一索引，既能从数据库层面保证无重复，还能加快EXISTS判断的速度：

CREATE UNIQUE NONCLUSTERED INDEX IX_GenNumbers_UniqueCombination
ON GenNumbers (Number1, Number2, Number3, Number4, Number5, Number6, Number7, Number8, Number9, Number10)

• 如果你要的是所有可能的组合（不是随机的），那随机生成的方式不适用，应该用笛卡尔积生成1-50的10位数组合，但注意50^10是9.7e16，远超过百万级，所以如果只是要百万级无重复随机组合，上面的方法完全够用。

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