How to create Temp table with SELECT * INTO tempTable FROM CTE sql 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 ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%'
    or EventEnumDays is null
order by EventID, PlannedDate
option (maxrecursion 0)

I would appreciate a point in the right direction or if I can create a temporary table from this CTE query

Mr Moose · Accepted Answer · 2016-07-25 12:53:11Z

The correct syntax looks like this:

Below is the whole query for reference

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(day, 1, PlannedDate),
           EventType
    from Calendar
    where EventRecurring = 1 and
          dateadd(day, 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
into tempblockeddates
from Calendar
where (PlannedDate >= GETDATE()) AND
      ',' + EventEnumDays + ',' like '%,' + cast(datepart(dw, PlannedDate) as char(1)) + ',%' or
      EventEnumDays is null
order by EventID, PlannedDate
option (maxrecursion 0);

The ordering of clauses is important:

with goes before select.
No semicolon line terminators in the middle of a query.
Subqueries are always surrounded by parentheses.
The final where probably doesn't do what you want. But I'm leaving your original logic. (I'm guessing you should have parentheses.)

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related