SQL Server CTE loop; insert all record together

You could use MERGE:

select * into #t1
from(
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va1'c1,'vb1'c2,'vc1'c3 union all
select 'va2'c1,'vb2'c2,'vc2'c3 union all
select 'va3'c1,'vb3'c2,'vc3'c3 union all
select 'va4'c1,'vb4'c2,'vc4'c3 
)t;

select * into #t2 from #t1 where 0=1;

;with tmp1 as(
    select t1.*, ROW_NUMBER()over(partition by t1.c1 order by(select null))r
    from #t1 t1
    left join #t2 t2 
      on t1.c1=t2.c1
    where t2.c1 is null 
),tmp2 as (
    select 0 n,*
    from tmp1
    union all
    select n+1 n,t1.c1,t1.c2,t1.c3,t1.r
    from tmp2 t1
    join tmp1 t2
      on t1.c1=t2.c1
     and t2.r=t1.r+1
    where n<10
)
MERGE #t2
USING tmp2
  ON #t2.c1 = tmp2.c1
WHEN NOT MATCHED THEN
  INSERT VALUES (tmp2.c1, tmp2.c2, tmp2.c3);

SELECT @@ROWCOUNT;
-- 103

DBFiddle Demo

---

EDIT:

Kudos to Bartosz Ratajczyk for examining this case:

It turns out it is related to lazy/eager table/index spooling. There are at least two more ways to force SQL Server to generate different execution plan:

a) By using TOP (100) PERCENT

DECLARE @n INT = 100;

;with tmp1 as (
    select t1.*,
           ROW_NUMBER()over(partition by t1.c1 order by(select null))r
    from #t1 t1
    left join #t2 t2 
      on t1.c1=t2.c1
    where t2.c1 is null 
),tmp2 as
(
    select 0 n,*
    from tmp1
    union all
    select n+1 n,t1.c1,t1.c2,t1.c3,t1.r
    from tmp2 t1
    join tmp1 t2
      on t1.c1=t2.c1
     and t2.r=t1.r+1
    where n<10
)
insert #t2
select TOP (@n) PERCENT c1, c2, c3  --,r
from tmp2

SELECT @@ROWCOUNT;

b) By using ORDER BY .. OFFSET 0 ROWS:

;with tmp1 as(
    select t1.*,
           ROW_NUMBER()over(partition by t1.c1 order by(select null))r
    from #t1 t1
    left join #t2 t2 
      on t1.c1=t2.c1
    where t2.c1 is null 
),tmp2 as
(
    select 0 n,*
    from tmp1
    union all
    select n+1 n,t1.c1,t1.c2,t1.c3,t1.r
    from tmp2 t1
    join tmp1 t2
      on t1.c1=t2.c1
     and t2.r=t1.r+1
    where n<10
)
insert #t2
select c1, c2, c3  --,r
from tmp2
ORDER BY 1 OFFSET 0 ROWS;

SELECT @@ROWCOUNT;

db<>fiddle demo2

---

Addendum: How does the recursive CTE work? by Bartosz Ratajczyk

You hit a peculiarity in MS SQL server's CTE implementation. It is not handled that way in all backends. You have to select first into a temporary cursor and then insert from it. ie:

SELECT *
INTO #t1
FROM(
    SELECT 'va1' c1, 'vb1' c2, 'vc1' c3
    UNION ALL
    SELECT 'va2' c1, 'vb2' c2, 'vc2' c3
    UNION ALL
    SELECT 'va3' c1, 'vb3' c2, 'vc3' c3
    UNION ALL
    SELECT 'va1' c1, 'vb1' c2, 'vc1' c3
    UNION ALL
    SELECT 'va2' c1, 'vb2' c2, 'vc2' c3
    UNION ALL
    SELECT 'va3' c1, 'vb3' c2, 'vc3' c3
    UNION ALL
    SELECT 'va1' c1, 'vb1' c2, 'vc1' c3
    UNION ALL
    SELECT 'va2' c1, 'vb2' c2, 'vc2' c3
    UNION ALL
    SELECT 'va3' c1, 'vb3' c2, 'vc3' c3
    UNION ALL
    SELECT 'va1' c1, 'vb1' c2, 'vc1' c3
    UNION ALL
    SELECT 'va2' c1, 'vb2' c2, 'vc2' c3
    UNION ALL
    SELECT 'va3' c1, 'vb3' c2, 'vc3' c3
    UNION ALL
    SELECT 'va4' c1, 'vb4' c2, 'vc4' c3
    )t;

SELECT * INTO #t2 FROM #t1 WHERE 0=1;

DECLARE @tmp TABLE(c1 VARCHAR(10), c2 VARCHAR(10), c3 VARCHAR(10));

WITH
    tmp1 AS (
                SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY t1.c1 ORDER BY(SELECT NULL)) r
                FROM #t1 t1
                     LEFT JOIN #t2 t2 ON t1.c1=t2.c1
                WHERE t2.c1 IS NULL
            ),
    tmp2 AS (
                SELECT 0 n, * FROM tmp1
                UNION ALL
                SELECT n+1 n, t1.c1, t1.c2, t1.c3, t1.r
                FROM tmp2 t1
                     JOIN tmp1 t2 ON t1.c1=t2.c1
                                 AND t2.r=t1.r+1
                WHERE n<10
            )
INSERT @tmp(c1, c2, c3)
SELECT c1, c2, c3 --,r
FROM tmp2;

INSERT #t2 SELECT * FROM @tmp;

SELECT * FROM #t2;

DROP TABLE #t1;
DROP TABLE #t2;