我的目标是从一个CTE中选择结果,并在同一过程中用另一个CTE.插入到另一个表中。怎么做?
我的错误是…
无效的对象名称xy。
我的查询是
WITH ds
(
Select a, b, c
from test1
),
xy
(
select d, e, f
from test2
where (uses conditions from ds)
)
Select *
from ds (the result set of ds, am exporting this to csv)
Insert into AuditTest
(
Select * from xy
)
CTE只适用于一个查询,但看起来您可以在每个查询中使用一个CTE:
WITH ds AS
(
Select a, b, c from test1
)
Select * from ds (the result set of ds, am exporting this to csv)
WITH xy AS
(
select d,e,f from test2 where (uses conditions from test1)
)
Insert into AuditTest
(
Select * from xy
)
实际上,您可以使用output子句来返回插入的行,同时插入和输出结果。
;WITH ds AS
(
Select a, b, c from test1
),
xy AS
(
select d, e, f from test2 where (uses conditions from ds)
)
Insert into AuditTest
output inserted.d, inserted.e, inserted.f
Select d, e, f from xy
或真正的测试
CREATE TABLE #Test (a int)
;WITH ds AS
(
Select 0 as a, 1 as b, 2 as c
),
xy AS
(
select a as d, b as e from ds
)
Insert into #Test
OUTPUT inserted.a
Select e from xy
您可以这样运行INSERT
,不能在cte
:之后运行多个查询
;WITH ds AS ( Select a, b, c
from test1
)
,xy AS ( select d,e,f
from test2
where (uses conditions from test1)
)
Insert into AuditTest
Select *
from xy
在这种情况下,使用临时表可能是有益的,因为否则您将多次重新运行查询。