Hey there, let's work through this batch insert scenario using a recursive CTE in SQL Server. First, let's align on the context: you've already created temp table #t1 with your duplicate records, and now you want to insert all those records into a target table (I'll use #t2 since you dropped it initially) in batches using a CTE loop.

Step 1: Prepare Your Target Table #

First, let's recreate #t2 to match the schema of #t1 (since you dropped it at the start):

CREATE TABLE #t2 (
    c1 VARCHAR(10),
    c2 VARCHAR(10),
    c3 VARCHAR(10)
);

Step 2: Recursive CTE for Controlled Batch Inserts #

Here's a practical recursive CTE approach to split your #t1 records into batches and insert them into #t2. We'll use a configurable batch size to avoid overwhelming your server with a single massive insert:

DECLARE @BatchSize INT = 1000; -- Tweak this based on your server's capacity
DECLARE @TotalRecords INT = (SELECT COUNT(*) FROM #t1);

WITH BatchRangeCTE AS (
    -- Anchor member: Define the first batch's row range
    SELECT
        1 AS StartRow,
        CASE WHEN @BatchSize > @TotalRecords THEN @TotalRecords ELSE @BatchSize END AS EndRow,
        1 AS BatchNumber

    UNION ALL

    -- Recursive member: Calculate ranges for subsequent batches
    SELECT
        EndRow + 1,
        CASE WHEN EndRow + @BatchSize > @TotalRecords THEN @TotalRecords ELSE EndRow + @BatchSize END,
        BatchNumber + 1
    FROM BatchRangeCTE
    WHERE EndRow < @TotalRecords
)
-- Insert each batch by joining to numbered rows from #t1
INSERT INTO #t2 (c1, c2, c3)
SELECT t.c1, t.c2, t.c3
FROM (
    SELECT
        c1, c2, c3,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM #t1
) t
JOIN BatchRangeCTE br ON t.RowNum BETWEEN br.StartRow AND br.EndRow
OPTION (MAXRECURSION 0); -- Disable default recursion limit for large datasets

Key Details to Keep in Mind #

• Batch Size Adjustment: Smaller batches reduce lock contention and transaction log bloat, but too many tiny batches add unnecessary overhead. Test sizes like 500, 1000, or 2000 to find what works for your environment.
• Ordering: Using ORDER BY (SELECT NULL) gives an arbitrary row order. If you need records inserted in a specific sequence, replace this with a real column from #t1 (e.g., ORDER BY c1, c2).
• MAXRECURSION: The default recursion limit is 100, so we use OPTION (MAXRECURSION 0) to handle cases where you have more than 100 batches.
• Log Optimization: If inserting into a permanent table, switch to the bulk-logged recovery model temporarily to minimize transaction log usage.

Quick Note on Alternatives #

While you specifically asked for a CTE-based solution, a WHILE loop is often more straightforward for batch inserts. But since you want to use a CTE, the above approach fits your requirement perfectly.

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