CODEWITHSUNDEEP
sqlsql-server-2008stored-procedures
user28455
user28455

Reputation: 455

SQL Server WITH statement

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
)

Upvotes: 26

Views: 196980

Answers (3)

Ceres
Ceres

Reputation: 3648

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

Upvotes: 8

Hart CO
Hart CO

Reputation: 34774

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.

Upvotes: 3

D Stanley
D Stanley

Reputation: 152501

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
)

Upvotes: 28

Related Questions