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

user_1122748 0
Viewed 1699325

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

common-table-expression sql-server sql
8 Answers

How to Use TempTable in Stored Procedure?

Here are the steps:

CREATE TEMP TABLE

-- CREATE TEMP TABLE 
Create Table #MyTempTable (
    EmployeeID int
);

INSERT TEMP SELECT DATA INTO TEMP TABLE

-- INSERT COMMON DATA
Insert Into #MyTempTable
Select EmployeeID from [EmployeeMaster] Where EmployeeID between 1 and 100

SELECT TEMP TABLE (You can now use this select query)

Select EmployeeID from #MyTempTable

FINAL STEP DROP THE TABLE

Drop Table #MyTempTable

I hope this will help. Simple and Clear :)

user_58837100

Here's one slight alteration to the answers of a query that creates the table upon execution (i.e. you don't have to create the table first):

SELECT * INTO #Temp
FROM (
select OptionNo, OptionName from Options where OptionActive = 1
) as X
user_18056370 
Select      Eventname, 
            count(Eventname) as 'Counts'
INTO        #TEMPTABLE                                                                                
FROM        tblevent
where       Eventname like 'A%'
Group by    Eventname
order by    count(Eventname)

Here by using the into clause the table is directly created

user_97699970

You should also be aware that there are GLOBAL temporary tables. These can be reference from another connection. They behave pretty much as described by early answers. To create one, simply prefix the table name with ##. That's what I was looking for; hope others that land here find that useful too.

user_118098360

You can also use View to create a temporary/virtual table CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition;

user_89283510
Related