CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
kombsh
kombsh

Reputation: 387

CTE in From clause of SQL Query

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.

Upvotes: 1

Views: 2252

Answers (2)

Amul Harad
Amul Harad

Reputation: 146

use below query



  Create Table #Temp(name1 text, name2 text)

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

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

    select * from #Temp2
    GO
    Drop Table #Temp
    Drop Table #Temp2

Upvotes: 1

Snehal
Snehal

Reputation: 1088

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

Upvotes: 1

Related Questions