如何从CTE查询中使用SELECT*创建TEMP表?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (53)

我有一个MSSQLCTE查询,我想从它创建一个临时表。我不知道该怎么做,因为它给了我Invalid Object name错误。

下面是供参考的整个查询

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)

如果我能从这个cte查询中创建一个临时表,我会很感激!

提问于
用户回答回答于

例子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

确保在使用后删除该表。

If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End
用户回答回答于

实际上,格式可以非常简单。

Select FieldA...FieldN 
into #MyTempTable 
from MyTable

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

扫码关注云+社区

领取腾讯云代金券