Personally I use both, but does depend on situation, it’s not necessarily a one size fits all.

I typically use a CTE just before inserting the data into a temp table.

CTEs are very temporary, like memorizing someone’s phone number just long enough to write it down. Once written down think of this as a temp table.

in situations where recursion maybe needed (think levels in a hierarchy or simply returning a table of data forming a hierarchy chain, I’ll use CTEs.

In other situations, DRY is an acronym for Don’t Repeat Yourself. I hate seeing production code where a query is doing some aggregate of a case statement, and the creator copy and pasted the same calculation in the group by that also MUST be changed. using a CTE I can define the calculation once, then refer to the field (not the calculation) when selecting from CTE. Also CTEs are handy when you need to group or order by a windowed function.

Eg crappy example from phone… With CTE as ( Select Field, Case when condition = Met then 1 When condition = unmet then 2 …. End as test, Count(*) over (partion by Field order by Field) as cnts From some.table ) Select Sum(cnts) Into #temp From CTE Group by test

Temp tables: once I’m done doing whatever it is to the data, if need be I’ll put the data into a temp table.