MSSQL中高效重复插入表数据的优化方案咨询

嘿，很高兴你喜欢Stack Overflow！咱们直接解决这个性能痛点——用WHILE循环重复40万次插入确实会把数据库拖得很慢，毕竟每次循环都要单独发起一次插入操作，带来大量的事务日志写入、锁竞争这类额外开销，累加起来就会导致严重的性能过载。

为什么你的尝试写法不可行 #

你提到的那个简化写法INSERT INTO tbl1 SELECT ID, Name, @C = COUNT(ID) FROM tbl2 WHERE @C < 3是无法实现需求的，SQL Server不支持在SELECT语句里这么混用变量赋值和查询逻辑，而且这种写法也没办法生成重复的行数据。

高效的批量插入方案 #

核心思路是一次性生成所有需要重复的行，然后执行一次（或少量几次）插入操作，避免逐次循环的开销。这里给你两种常用的方法：

方法1：用递归CTE生成重复次数序列

递归CTE可以快速生成从1到400000的数字序列，然后和你的tbl2做交叉连接，就能得到40万份tbl2的数据：

WITH Numbers AS (
    SELECT 1 AS Num
    UNION ALL
    SELECT Num + 1
    FROM Numbers
    WHERE Num < 400000
)
INSERT INTO tbl1 (ID, Name)
SELECT t.ID, t.Name
FROM tbl2 t
CROSS JOIN Numbers
OPTION (MAXRECURSION 0); -- 递归CTE默认最大深度是100，必须开这个选项才能生成40万条

方法2：用系统表生成数字序列（性能更优）

如果递归CTE在生成超大量数字时有点慢，可以利用系统表的笛卡尔积快速生成序列，不需要递归：

WITH Numbers AS (
    SELECT TOP 400000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
    FROM sys.all_columns c1
    CROSS JOIN sys.all_columns c2
)
INSERT INTO tbl1 (ID, Name)
SELECT t.ID, t.Name
FROM tbl2 t
CROSS JOIN Numbers;

超大量数据的分批插入（可选） #

如果一次性插入200万行（5*40万）导致事务日志过大，或者数据库资源有限，可以考虑分批插入，比如每次插入10万份tbl2的数据，既控制日志大小，又比原来的逐次循环高效得多：

DECLARE @BatchSize INT = 100000;
DECLARE @TotalIterations INT = 400000;
DECLARE @CurrentIteration INT = 0;

WHILE @CurrentIteration < @TotalIterations
BEGIN
    WITH Numbers AS (
        SELECT TOP (@BatchSize) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Num
        FROM sys.all_columns c1
        CROSS JOIN sys.all_columns c2
    )
    INSERT INTO tbl1 (ID, Name)
    SELECT t.ID, t.Name
    FROM tbl2 t
    CROSS JOIN Numbers;

    SET @CurrentIteration += @BatchSize;
    COMMIT; -- 显式提交每个批次的事务，避免日志过度积累
END

这些方法都是把原来的40万次插入操作，变成1次或4次插入，性能提升会非常明显。

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