Using multiple CTE with multiple temp tables-CodePudding

In Windows Server, I am trying to gather data using multiple CTEs insert them into a few temp tables to later on perform a join. Below is what I got. :

------TEMP TABLE SET UP------
IF EXISTS   (
           SELECT *
           FROM tempdb.dbo.sysobjects
           WHERE id = Object_id(N'tempdb..#LEFT')
           )
       BEGIN
           DROP TABLE #LEFT
       END
IF EXISTS   (
           SELECT *
           FROM tempdb.dbo.sysobjects
           WHERE id = Object_id(N'tempdb..#RIGHT')
           )
       BEGIN
           DROP TABLE #RIGHT
       END
------TEMP TABLE SET UP END------
------CTE SET UP------
; with 
 CTEfirst (1a, b, c, d) as 
 (select 1a, b, c, d from tableA)
, CTEone (a, b, c) as
 (select a, b, c from table1)
),
 CTEtwo (a, b, c) as (
 (select a, b, c from table2)
),
   CTEthree (a, b, c) as (
 (select a, b, c from table3)
------CTE SET UP END------
select * into #LEFT from CTEone 
union
select * from CTEtwo 
union
select * from CTEthree 
-----------------------------
/*At this point I am getting the issue to recognize CTEfirst when attempting to insert data into #RIGHT temp table unless I move the below portion below the previous section (prior to the unions) but then would encounter the issue of the overall query not recognizing the next CTE, CTEone.*/
select * into #RIGHT from CTEfirst

Thank you

CodePudding user response：

you have declared cte but it is empty

; with 
CTEfirst (a, b, c, d)
as ( select ...) --<-- missing cte definiation here 
, CTEone (a, b, c) as
 (select a, b, c from table1)
),

CodePudding user response：

You cannot refer to the same CTE for more than one unattached select statement. Your first query ends when you insert into #left. After that you cannot run a new select statement referring to the same (unattached) CTEs. Think of CTEs as reformatted sub-queries. If you want data loaded into multiple temp tables, I wouldn't use CTEs in the first place. Just insert into the temp tables directly.