How to create Temp table with SELECT * INTO tempTable FROM CTE Query

Question

I have a MS SQL CTE query from which I want to create a temporary table. I am not sure how to do it as it gives an `Invalid Object name` error. Below is the whole query for reference ``` SELECT * INTO TEMPBLOCKEDDATES FROM ;with Calendar as ( select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, EventStartDate as PlannedDate ,EventType from EventCalender where EventActive = 1 AND LanguageID =1 AND EventBlockDate = 1 union all select EventID, EventTitle, EventStartDate, EventEndDate, EventEnumDays,EventStartTime,EventEndTime, EventRecurring, dateadd(dd, 1, PlannedDate) ,EventType from Calendar where EventRecurring = 1 and dateadd(dd, 1, PlannedDate) <= EventEndDate ) select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle ,EventType from Calendar where (PlannedDate >= GETDATE()) AND ',' + E…

Accepted Answer

Sample DDL

```
create table #Temp
(
    EventID int, 
    EventTitle Varchar(50), 
    EventStartDate DateTime, 
    EventEndDate DatetIme, 
    EventEnumDays int,
    EventStartTime Datetime,
    EventEndTime DateTime, 
    EventRecurring Bit, 
    EventType int
)
```

```
;WITH Calendar
AS (SELECT /*...*/)

Insert Into #Temp
Select EventID, EventStartDate, EventEndDate, PlannedDate as [EventDates], Cast(PlannedDate As datetime) AS DT, Cast(EventStartTime As time) AS ST,Cast(EventEndTime As time) AS ET, EventTitle
,EventType from Calendar
where (PlannedDate >= GETDATE()) AND ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%'
    or EventEnumDays is null
```

Make sure that the table is deleted after use

```
If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End
```

How to create Temp table with SELECT * INTO tempTable FROM CTE Query

Solution

Problem

Related problems