How to Use 2 CTE in a Single SQL Query - Use Temp Table Instead

Question: I found myself in need to use two CTE in one single SQL Query, however, I wasn't successful because the query threw an error.

Solution: After a couple of minutes of troubleshooting, I went back to the drawing board and brainstormed the problem. The solution was to use a #Temp table, since the SQL Server Temp table can only be accessed within the Session in which it was created, I could access the temp how many times I want.

However, the limit of using a CTE in #SQL Query is that the CTE can only be accessed once by an execting transaction or line of SQL code. This creates all sorts of problems, of course, this is based on how you intend to use it.

See the example below:

-- Example
Create proc OneCTEandTempTable
as
with cte as (

--Your Code

)
--Select into a temp table from cte, this is make sure you can access the data several times
--within the session
select * into #OneCTETempTable from cte

--Then query the heck out of the temp table
select top 100  * from #OneCTETempTable

In conclusion, go with the Temp table or you could create a #CTE then query the data from the CTE into the Temp Table then access the temp table many times within the executing session.

I hope this helps you in deciding whether you want to use two CTE in one Single SQL Query, it does leave us a comment below.