sql server - CTE in From clause of SQL Query

Question

I need to use CTE query inside from clause of SQL Query

See this example:

Drop Table #Temp
Drop Table #Temp2
Create Table #Temp(name1 text, name2 text)

Insert INTO #Temp Values ('test','test')
Insert INTO #Temp Values ('test','test')

select * into #Temp2
from #Temp

Select * from #Temp2

Here, I am just inserting rows into temp table 'Temp2' from selecting records from Temp... this is working fine...

But my need is, have to use CTE inside from clause.. like

select * into #Temp2
from (;With CTE as ( Select * from #Temp) select * from CTE)

Please don't encourage me to separate CTE query..because, I can't control that part of query since it is being provided by other system.

select * into #Temp2
from ("Query Provided by Other System")

So the "Query Provided by Other System" may or may not be the CTE query.

Answer 1

Check with below syntax, its worked for me and i hope you are looking for same:

With CTE as ( Select * from #Temp) 
select * into #Temp2 from CTE