使用CTE作为块循环? [英] Using CTE as a block loop ?
问题描述
除了 while循环
和光标
如何使用CTE表执行 n
block 操作?
例如:
使用cte AS(
选择1 AS n
UNION所有
从cte
中选择n + 1
n + 1 <== 10
)
插入tbl1(id)从cte中选择n-直到一切正常。
插入tbl2(id),从cte-{notice tbl2}中选择n。在此失败(因为detecle后cte的使用必须紧随其后)
但是我能做些什么,所以第二行也包括在内?
p.s。 (除了将其保存为临时表外)
通常您不能。
仅对下一条语句有效。如果要对多个语句重用定义或自己将其具体化为临时表/表变量以重用结果,则可以使用相同的定义定义视图。 / p>
对于您问题中的特定情况,您可以在一条语句中全部完成。
与cte
AS(选择1 AS n
UNION ALL
SELECT n + 1
FROM cte
WHERE n + 1 <= 10)
插入tbl1
(id)
OUTPUT INSERTED.id
插入tbl2(id)
从cte
SELECT n
Besides while loop
and Cursors
How can I use CTE table to do n
block operations ?
for example :
WITH cte AS (
SELECT 1 AS n
UNION ALL
SELECT n+1
FROM cte
WHERE n+1<= 10
)
INSERT INTO tbl1 (id) select n from cte --till here it's all ok.
INSERT INTO tbl2 (id) select n from cte --{notice tbl2} .fail here ( cause cte usages must come right after the decleration)
But is there anything I can do so the second line will be also include ?
p.s. ( beside keeping it as a temp table)
In general you can't.
It is only valid for the next statement. You could define a view with the same definition if you want to reuse the definition for multiple statements or materialise it into a temporary table/ table variable yourself to reuse the results.
For the specific case in your question you could do it all in one statement though.
WITH cte
AS (SELECT 1 AS n
UNION ALL
SELECT n + 1
FROM cte
WHERE n + 1 <= 10)
INSERT INTO tbl1
(id)
OUTPUT INSERTED.id
INTO tbl2(id)
SELECT n
FROM cte
这篇关于使用CTE作为块循环?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!