SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

INSERT INTO #t1
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; 
4 rows affected

with tmp1 as(
    select t1.*, ROW_NUMBER()over(partition by t1.c1 order by t1.c1)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<2
)
SELECT * FROM tmp2


n
c1
c2
c3
r


0
va1
vb1
vc1
1

0
va1
vb1
vc1
2

0
va2
vb2
vc2
1

0
va3
vb3
vc3
1

1
va1
vb1
vc1
1

2
va1
vb1
vc1
1


with tmp1 as (
    select t1.*,ROW_NUMBER()over(partition by t1.c1 order by t1.c1)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<2
)
INSERT INTO #t2(c1, c2, c3)   -- only difference is INSERT INTO
SELECT c1, c2, c3 FROM tmp2
ORDER BY 1 OFFSET 0 ROWS

SELECT @@ROWCOUNT;

SELECT * FROM #t2;


(No column name)


6




c1
c2
c3


va1
vb1
vc1

va1
vb1
vc1

va1
vb1
vc1

va1
vb1
vc1

va2
vb2
vc2

va3
vb3
vc3

n	c1	c2	c3	r
0	va1	vb1	vc1	1
0	va1	vb1	vc1	2
0	va2	vb2	vc2	1
0	va3	vb3	vc3	1
1	va1	vb1	vc1	1
2	va1	vb1	vc1	1

n	c1	c2	c3	r
0	va1	vb1	vc1	1
0	va1	vb1	vc1	2
0	va2	vb2	vc2	1
0	va3	vb3	vc3	1
1	va1	vb1	vc1	1
2	va1	vb1	vc1	1

n	c1	c2	c3	r
0	va1	vb1	vc1	1
0	va1	vb1	vc1	2
0	va2	vb2	vc2	1
0	va3	vb3	vc3	1
1	va1	vb1	vc1	1
2	va1	vb1	vc1	1