Using Temp Table and With Clause and Union - MS SQL SERVER

Is it possible to create a Temp Table when using a With clause and Union
eg
Create Table #T1
(

)
Insert into T1
With test as (
Select ....
From
)
Select
..
From test
UNION
Select
...
From
Table

philsivyerAsked:
Who is Participating?
 
RiteshShahConnect With a Mentor Commented:
look at this:



Create Table #T1
(
id int
)


;With test as 
(
Select 1 as T
)
insert into #T1
Select T From test
union
select 2 as T

select * from #t1

drop table #T1

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:

If I am right...

You can use the CTE only after it's declared.

ie.,

With test as
(
Select ...
)

Only in one sql query you can refer CTE table after declaration.

So answer to question is it is not possible to do like that

So this will only work
Create Table #T1
(

)

With test as
(
Select ....
From
)

Insert into T1
Select
..
From test
UNION
Select
...
From
Table

Raj
0
 
philsivyerAuthor Commented:
RiteshShah

Yep - that works

Thanks
0
 
philsivyerAuthor Commented:
Thanks
0
All Courses

From novice to tech pro — start learning today.