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 ) 3 Answers
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 ) You actually can do both the insert and output the results using the OUTPUT clause to return the inserted rows.
;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 or a real test
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 You can run the INSERT thusly, you can't run multiple queries after your 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 In this situation using temporary tables may be beneficial since you'll be re-running a query multiple times otherwise.
