SQL Server WITH 语句

Question

我的目标是从一个 CTE 中选择结果，并在同一过程中用另一个 CTE 插入到另一个表中。怎么做？

我的错误是...

invalid object name 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
)

Answer 1

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
)