如何从CTE查询中将SELECT *创建到tempTable中

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.

我有一个MS SQL CTE查询，我想从中创建一个临时表。我不知道该怎么做，因为它会产生一个无效的对象名错误。

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

如果我能从这个CTE查询中创建一个临时表，我将非常感激。

5 个解决方案

#1

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

#2

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的结果创建。

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.

所以，除非你想要不同的类型或对定义非常严格，否则要保持简单。还要注意，存储过程中创建的任何临时表都会在存储过程执行完毕时自动删除。如果存储过程A创建一个临时表并调用存储过程B，那么B将能够使用创建的临时表。

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

然而，通常认为显式删除创建的每个临时表是一种良好的编码实践。

#3

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

选择……需要从CTE中选择INTO。

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

#4

How to Use TempTable in Stored Procedure?

如何在存储过程中使用TempTable ?

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)

选择TEMP表(现在可以使用这个SELECT查询)

Select EmployeeID from #MyTempTable

FINAL STEP DROP THE TABLE

最后一步放下桌子

Drop Table #MyTempTable

I hope this will help. Simple and Clear :)

我希望这能有所帮助。简单而清晰:)

#5

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

在这里，通过使用into子句，可以直接创建表