如何在SQL Server中按指定次数重复插入数据？

嘿，这个需求其实挺常见的，SQL Server里有几种靠谱的办法能帮你实现按[No. of Entries]的数值重复插入对应行的需求，我给你详细拆解一下：

方法一：使用递归CTE（通用表表达式） #

这是最灵活的方法之一，不需要依赖额外的系统表或自定义表，适合中小数据量的场景。

示例代码 #

WITH RecursiveEntries AS (
    -- 初始查询：获取所有需要重复插入的记录及剩余次数
    SELECT 
        A_NUM, 
        Address, 
        [No. of Entries] AS RemainingEntries
    FROM Table_1
    WHERE [No. of Entries] > 0 -- 排除不需要插入的次数为0的记录
    UNION ALL
    -- 递归部分：每次将剩余次数减1，直到剩余次数为1（因为初始行已经算1次）
    SELECT 
        A_NUM, 
        Address, 
        RemainingEntries - 1
    FROM RecursiveEntries
    WHERE RemainingEntries > 1
)
-- 将生成的重复记录插入目标表
INSERT INTO Table_entries (A_NUM, Address)
SELECT A_NUM, Address
FROM RecursiveEntries
OPTION (MAXRECURSION 0); -- 取消递归层数限制，应对次数超过100的情况

原理说明 #

• 初始查询从Table_1中取出所有需要处理的行，同时保留需要重复的次数作为RemainingEntries。
• 递归部分会不断生成新的行，每次把RemainingEntries减1，直到数值变为1为止——这样每一条原始记录就会生成[No. of Entries]条重复行。
• 最后把这些重复行批量插入到Table_entries中。

方法二：使用数字表（Tally Table） #

如果你的数据量很大（比如单条记录需要插入上万次），数字表的效率会比递归CTE更高。你可以用系统表临时生成数字序列，或者创建一个永久的数字表。

用系统表临时生成数字序列 #

SQL Server的master..spt_values系统表包含了连续的数字，可以用来快速生成序列：

INSERT INTO Table_entries (A_NUM, Address)
SELECT 
    t.A_NUM, 
    t.Address
FROM Table_1 t
-- 关联数字序列，只取小于等于目标次数的数字
JOIN (
    -- 生成连续的数字序列
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM master..spt_values
) nums ON nums.N <= t.[No. of Entries];

如果master..spt_values的数字数量不够（默认最多到2047），可以用交叉连接生成更多数字：

INSERT INTO Table_entries (A_NUM, Address)
SELECT 
    t.A_NUM, 
    t.Address
FROM Table_1 t
JOIN (
    -- 交叉连接生成更大的数字序列（最多可生成约400万条）
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N
    FROM master..spt_values v1
    CROSS JOIN master..spt_values v2
) nums ON nums.N <= t.[No. of Entries];

原理说明 #

• 先生成一个连续的数字序列N，然后将Table_1中的每一行和这个序列关联，只要N小于等于[No. of Entries]，就会生成对应次数的重复行。
• 这种方法是集合式操作，比递归的逐行处理效率更高，适合大数据量场景。

注意事项 #

• 确保Table_entries的字段类型和Table_1中的A_NUM、Address完全匹配，避免插入时出现类型转换错误。
• 如果[No. of Entries]可能为0，一定要加上WHERE [No. of Entries] > 0的过滤条件，避免插入无效的空行。
• 如果你需要频繁处理这类需求，建议创建一个永久的数字表（比如包含1到100000的数字），使用起来会更高效。

内容的提问来源于stack exchange，提问作者Byron Louise Hudkin Festin