如何按指定次数重复执行带递增StorageRowNo的SQL INSERT语句

需求说明 #

需要向[dbo].[StorageRow]表插入数据，要求：

• 按指定次数执行插入操作，每次插入的StorageRowNo自增1
• 避免插入重复的(StorageRowNo, StorageID)组合

现有单条插入语句：

INSERT [dbo].[StorageRow]
    SELECT StorageRowNo, StorageID
    FROM (VALUES (1, 2)) V (StorageRowNo, StorageID)
    WHERE NOT EXISTS (SELECT 1 
                      FROM [dbo].[StorageRow] C
                      WHERE C.StorageRowNo = V.StorageRowNo 
                        AND C.StorageID = V.StorageID);

当指定插入次数为3时，预期插入结果如下：

优化实现方案 #

直接循环执行单条语句效率较低，推荐用批量生成序列+一次性插入的方式，既满足自增要求，又能通过NOT EXISTS过滤重复数据。

方法1：递归CTE生成连续序列（适用于SQL Server 2008及以上） #

假设要插入N次（示例中N=3），固定StorageID=2：

DECLARE @InsertCount INT = 3; -- 指定插入次数
DECLARE @TargetStorageID INT = 2; -- 目标StorageID

WITH SequenceCTE AS (
    SELECT 1 AS StorageRowNo
    UNION ALL
    SELECT StorageRowNo + 1
    FROM SequenceCTE
    WHERE StorageRowNo < @InsertCount
)
INSERT INTO [dbo].[StorageRow] (StorageRowNo, StorageID)
SELECT s.StorageRowNo, @TargetStorageID
FROM SequenceCTE s
WHERE NOT EXISTS (
    SELECT 1 
    FROM [dbo].[StorageRow] c
    WHERE c.StorageRowNo = s.StorageRowNo 
      AND c.StorageID = @TargetStorageID
);

方法2：系统表生成序列（适合大数量插入） #

如果需要插入的次数较多（比如上万次），递归CTE可能性能不足，可借助系统表生成序列：

DECLARE @InsertCount INT = 3;
DECLARE @TargetStorageID INT = 2;

INSERT INTO [dbo].[StorageRow] (StorageRowNo, StorageID)
SELECT TOP (@InsertCount) 
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS StorageRowNo,
    @TargetStorageID
FROM sys.all_objects s
WHERE NOT EXISTS (
    SELECT 1 
    FROM [dbo].[StorageRow] c
    WHERE c.StorageRowNo = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
      AND c.StorageID = @TargetStorageID
);

方案优势 #

• 一次性批量插入，比循环执行单条语句效率更高
• 保留原语句的NOT EXISTS逻辑，确保不会插入重复的(StorageRowNo, StorageID)组合
• 通过变量控制插入次数和目标StorageID，灵活性更强

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