如何在SQL Server中按Inserts列循环插入新行？

原表结构与数据 #

假设基础表名为BaseTable，其结构和数据如下：

需求说明 #

遍历表中每一行，根据Inserts列的数值插入对应数量的新行：

• 新行的DAY_ID = 原行DAY_ID - N（N从1到Inserts的数值）
• 新行的Value与原行保持一致
• 新行的Inserts固定为0

比如原表中DAY_ID=5、Value=8的行Inserts=2，需插入两行：DAY_ID=4、Value=8、Inserts=0和DAY_ID=3、Value=8、Inserts=0；DAY_ID=2、Value=4的行Inserts=1，需插入一行：DAY_ID=1、Value=4、Inserts=0。

解决方案（优先用集合操作，效率更高） #

SQL Server中不推荐用循环处理这类批量数据操作，集合式操作的执行效率远高于循环。以下提供两种常用实现方式：

方法1：递归CTE生成待插入行 #

WITH RecursiveInserts AS (
    -- 锚点：筛选出需要生成新行的原数据
    SELECT 
        DAY_ID,
        Value,
        Inserts,
        1 AS CurrentStep
    FROM BaseTable
    WHERE Inserts > 0

    UNION ALL

    -- 递归：生成每一行对应的N条新行
    SELECT 
        DAY_ID,
        Value,
        Inserts,
        CurrentStep + 1
    FROM RecursiveInserts
    WHERE CurrentStep < Inserts
)
-- 插入新行到原表（可替换为目标表）
INSERT INTO BaseTable (DAY_ID, Value, Inserts)
SELECT 
    DAY_ID - CurrentStep,
    Value,
    0 AS Inserts
FROM RecursiveInserts
OPTION (MAXRECURSION 0); -- 若Inserts数值过大，需开启无限递归

方法2：利用系统数字表生成序列 #

借助SQL Server自带的master..spt_values表生成连续数字，匹配需要插入的行数：

-- 插入新行
INSERT INTO BaseTable (DAY_ID, Value, Inserts)
SELECT 
    bt.DAY_ID - sv.number,
    bt.Value,
    0 AS Inserts
FROM BaseTable bt
JOIN master..spt_values sv 
    ON sv.type = 'P' 
    AND sv.number BETWEEN 1 AND bt.Inserts
WHERE bt.Inserts > 0;

注意：master..spt_values的number默认范围是0到2047，如果你的Inserts数值超过2047，建议自定义一个数字表来生成更大的序列。

验证结果 #

执行上述任一方法后，原表数据会更新为：

循环实现示例（不推荐，仅作参考） #

如果一定要用循环实现，可通过游标或WHILE循环完成，但处理大量数据时效率较低：

DECLARE @DAY_ID INT, @Value INT, @Inserts INT, @i INT;

-- 声明游标遍历需要插入新行的记录
DECLARE cur CURSOR FOR 
SELECT DAY_ID, Value, Inserts 
FROM BaseTable 
WHERE Inserts > 0;

OPEN cur;
FETCH NEXT FROM cur INTO @DAY_ID, @Value, @Inserts;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @i = 1;
    WHILE @i <= @Inserts
    BEGIN
        INSERT INTO BaseTable (DAY_ID, Value, Inserts)
        VALUES (@DAY_ID - @i, @Value, 0);
        SET @i = @i + 1;
    END
    FETCH NEXT FROM cur INTO @DAY_ID, @Value, @Inserts;
END

CLOSE cur;
DEALLOCATE cur;

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