SQL Server CTE 循环;将所有记录插入在一起

Question

>我有这种情况：

drop table #t1;
drop table #t2
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
)
--insert #t2
select c1, c2, c3  --,r
from tmp2

当我运行它时，它会选择一切正常(103 条记录(。

问题是当我将此代码插入 #t2(13条记录!!(

我认为 SQL 会逐步运行并在运行过程中插入记录，而不是我在 tmp1 中的条件已经结束......

如何解决？

我的目标是检查数据是否存在，而不是循环和插入结果......但是 SQL 在第一个周期后停止...

Answer 1

您可以使用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 演示

---

编辑：

感谢Bartosz Ratajczyk审查此案：

事实证明，它与惰性/急切表/索引假脱机有关。至少还有两种方法可以强制 SQL Server 生成不同的执行计划：

a( 通过使用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( 通过使用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<>小提琴演示2

---

附录：递归CTE如何工作？作者：Bartosz Ratajczyk

Answer 2

你在MS SQL服务器的CTE实现中遇到了一个特点。并非所有后端都以这种方式处理它。您必须首先选择临时光标，然后从中插入。即：

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;