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 ',' + 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

Here is how to do it http://stackoverflow.com/questions/3306096/combining-insert-into-and-with-cte — Luxspes, Jul 15 '12 at 10:48
@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.. — Learning, Jul 15 '12 at 13:12

score 316 · Accepted Answer · edited Nov 30 '15 at 10:15

316

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

edited Nov 30 '15 at 10:15

Balicanta

109
6

answered Jul 15 '12 at 11:07

10

Why the double period? Is that a typo? – Mike Cole Jun 24 '16 at 18:47
30

The .. is to omit specifying the schema. For ex tempdb.dbo.#temp. Instead of that we can type tempdb..#temp. – sam Jun 29 '16 at 19:30
16

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. – DaveInAZ Jan 17 '18 at 17:52
re: schema names, the [docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15#temporary-tables) 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._ – JvR Apr 29 '22 at 07:20

score 260 · Answer 2 · edited Jun 15 '15 at 06:43

260

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.

edited Jun 15 '15 at 06:43

ani627

5,578
8
39
45

answered Jan 06 '14 at 01:50

Rohit

3,059
1
14
4

4

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"_. – TomasMolina Nov 25 '16 at 19:07
6

@Kurapika the duration of the connection – Jonesopolis Mar 14 '17 at 14:37
22

The fact we do not need to explicitly create the table before using it IS the most relevant fact in this answer. Thanks! – Alfabravo Mar 14 '18 at 19:25

score 47 · Answer 3 · answered Nov 06 '17 at 20:13

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

This doesn't answer the question. The OP specifically asked how to do it with Select Into, and this response does not do that. — DaveInAZ, Jan 17 '18 at 17:50

score 32 · Answer 4 · answered Jul 15 '12 at 10:54

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)

score 18 · Answer 5 · answered Apr 20 '20 at 22:16

18

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

answered Apr 20 '20 at 22:16

John Gilmer

831
1
11
18

not sure why this isn't the accepted answer, but thank you! – d0rf47 Feb 25 '22 at 17:07

score 11 · Answer 6 · edited May 10 '18 at 09:48

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

edited May 10 '18 at 09:48

zx8754

52,746
12
114
209

answered May 10 '18 at 09:46

linette J Sebastian

129
1
2

4

How is this different from existing answers? – zx8754 May 10 '18 at 09:50

Freond · Answer 7 · 2022-11-12T17:44:52.127

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.

edited Nov 12 '22 at 17:44

answered Apr 21 '22 at 15:21

Freond

64
10

score 0 · Answer 8 · answered Jun 04 '21 at 12:30

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;

answered Jun 04 '21 at 12:30

Abdullah Ayoub

39
4

3

This is very vague to be an answer – Vega Jun 04 '21 at 14:33

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

8 Answers8

Sample DDL

Linked

Related