260

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

Improve this question
2
  • Here is how to do it stackoverflow.com/questions/3306096/… Commented Jul 15, 2012 at 10:48
  • 2
    @RGI, Both answers will work for my case, I gave him Martin, up-vote since i can choose only one answers. I appreciate your answer. I gave your answer preference over his as you had mentioned deletion part of temp query also. Up-Vote for your also.. Commented Jul 15, 2012 at 13:12

8 Answers 8

Reset to default
326

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Why the double period? Is that a typo?
The .. is to omit specifying the schema. For ex tempdb.dbo.#temp. Instead of that we can type tempdb..#temp.
This doesn't answer the question. The OP specifically asked how to do it with Select Into, and this response does not do that. It's a good answer, but it's not the right answer.
re: schema names, the docs also say: If you include a schema_name when you create or access a temporary table, it is ignored. All temporary tables are created in the dbo schema.
288

Really the format can be quite simple - sometimes there's no need to predefine a temp table - it will be created from results of the select.

Select FieldA...FieldN 
into #MyTempTable 
from MyTable

So unless you want different types or are very strict on definition, keep things simple. Note also that any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temp table and calls stored procedure B, then B will be able to use the temporary table that A created.

However, it's generally considered good coding practice to explicitly drop every temporary table you create anyway.

Improve this answer

3 Comments

How long is available the temporary table in the data base after execution if i not remove it using drop table in my code? because i twice execute the code select * into #temp but, the second time the execution deliver a error : "The table #temp already exists in data base".
@Kurapika the duration of the connection
The fact we do not need to explicitly create the table before using it IS the most relevant fact in this answer. Thanks!
50

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 :)

Improve this answer

1 Comment

This doesn't answer the question. The OP specifically asked how to do it with Select Into, and this response does not do that.
33

The SELECT ... INTO needs to be in the select from the CTE.

;WITH Calendar
     AS (SELECT /*... Rest of CTE definition removed for clarity*/)
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 /* <---- INTO goes here*/        
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)
Improve this answer

Comments

20

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
Improve this answer

1 Comment

not sure why this isn't the accepted answer, but thank you!
11 
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

Improve this answer

1 Comment

How is this different from existing answers?
1

You should also be aware that there are GLOBAL temporary tables. These can be referenced 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.

Improve this answer

Comments

0

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

Improve this answer

1 Comment

This is very vague to be an answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.