SQL Server WITH statement

sqlsql-server-2008stored-procedures

My goal is to select result from one CTE and insert into other table with another CTE in the same procedure. How to do it?

My error is…

invalid object name xy.

My query is

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
)

Best Answer

A CTE is only good for one query, but it looks like you can use a CTE in each query:

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
)