递归CTE触发最大递归错误后，如何存储生成的结果集？

问题原因 #

你当前的写法中，当递归CTE触发MAXRECURSION限制错误时，整个INSERT操作会被回滚。因为SQL Server会将整个CTE查询作为原子操作执行，错误发生时会撤销所有已完成的插入，所以目标表中不会保留任何已生成的结果。

可行解决方案 #

方案一：用WHILE循环替代递归CTE #

改用循环逐步生成数据并插入，每一次插入都是独立操作，即使中途触发限制或错误，已插入的数据也会被保留。

示例代码：

DECLARE @StartNum INT = 1;
DECLARE @EndNum INT = 40000;
DECLARE @CurrentNum INT = @StartNum;

-- 确保目标表存在
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'temp23')
BEGIN
    CREATE TABLE temp23 (Number INT);
END

-- 循环插入数据
WHILE @CurrentNum <= @EndNum
BEGIN
    INSERT INTO temp23 (Number) VALUES (@CurrentNum);
    SET @CurrentNum = @CurrentNum + 1;
    
    -- 模拟MAXRECURSION限制，达到次数后抛出错误并停止
    IF @CurrentNum - @StartNum >= 100
    BEGIN
        RAISERROR('达到递归次数限制', 16, 1);
        BREAK;
    END
END

-- 捕获错误信息
IF @@ERROR <> 0
BEGIN
    SELECT SUSER_SNAME(),ERROR_NUMBER(),ERROR_STATE(),ERROR_SEVERITY(),ERROR_LINE(),ERROR_PROCEDURE(), 
  ERROR_MESSAGE();
END

方案二：分批次执行递归CTE #

如果一定要用递归CTE，可以分批次生成数据并插入，每一批次的递归都是独立的原子操作，某一批次出错时，之前批次的插入结果会被保留。

示例代码：

DECLARE @BatchSize INT = 100;
DECLARE @CurrentStart INT = 1;
DECLARE @EndNum INT = 40000;

-- 确保目标表存在
IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'temp23')
BEGIN
    CREATE TABLE temp23 (Number INT);
END

WHILE @CurrentStart <= @EndNum
BEGIN
    BEGIN TRY
        WITH NumbersCTE AS (
            SELECT @CurrentStart AS Number
            UNION ALL
            SELECT Number + 1 FROM NumbersCTE WHERE Number < @CurrentStart + @BatchSize - 1
        )
        INSERT INTO temp23
        SELECT * FROM NumbersCTE OPTION (MAXRECURSION @BatchSize);
        
        SET @CurrentStart = @CurrentStart + @BatchSize;
    END TRY
    BEGIN CATCH
        SELECT SUSER_SNAME(),ERROR_NUMBER(),ERROR_STATE(),ERROR_SEVERITY(),ERROR_LINE(),ERROR_PROCEDURE(), 
  ERROR_MESSAGE();
        BREAK;
    END CATCH
END

关键说明 #

• 递归CTE的执行是原子性的，一旦触发错误，所有已生成的结果都会被丢弃，无法直接捕获中途数据。
• 循环或分批次的方式可以避开原子性限制，确保已完成的插入操作不会被回滚。

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