Cómo crear una tabla Temporal con SELECT * EN tempTable DESDE la consulta CTE

Tengo una consulta CTE MS SQL desde la que quiero crear una tabla temporal. No estoy seguro de cómo hacerlo, ya que da un error Invalid Object name.

A continuación se muestra toda la consulta para referencia

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)

Agradecería un punto en la dirección correcta o si puedo crear una tabla temporal a partir de esta consulta CTE

Author: DarkAjax, 2012-07-15
Source

5 answers

Muestra 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

Asegúrese de que la tabla se elimina después de su uso

If(OBJECT_ID('tempdb..#temp') Is Not Null)
Begin
    Drop Table #Temp
End
 161
Author: Balicanta,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-11-30 10:15:13

Realmente el formato puede ser bastante simple - a veces no hay necesidad de predefinir una tabla temporal - se creará a partir de los resultados de la selección.

Select FieldA...FieldN 
into #MyTempTable 
from MyTable

Así que, a menos que desee diferentes tipos o sea muy estricto en la definición, mantenga las cosas simples. Tenga en cuenta también que cualquier tabla temporal creada dentro de un procedimiento almacenado se elimina automáticamente cuando el procedimiento almacenado termina de ejecutarse. Si el procedimiento almacenado A crea una tabla temporal y llama al procedimiento almacenado B, entonces B podrá usar tabla temporal que ha creado.

Sin embargo, generalmente se considera una buena práctica de codificación eliminar explícitamente cada tabla temporal que cree de todos modos.

 95
Author: Rohit,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-15 06:43:26

El SELECT ... INTO debe estar en el select del 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)
 21
Author: Martin Smith,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-15 10:54:45

¿Cómo usar TempTable en el Procedimiento Almacenado?

Aquí están los pasos:

CREAR TABLA TEMPORAL 

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

INSERTE LOS DATOS DE SELECCIÓN TEMPORAL EN LA TABLA TEMPORAL

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

SELECT TEMP TABLE (Ahora puede usar esta consulta select) 

Select EmployeeID from #MyTempTable

PASO FINAL ELIMINAR LA TABLA

Drop Table #MyTempTable

Espero que esto ayude. Simple y Claro:)

 13
Author: Manjunath Bilwar,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-11-06 20:13:06 
Select      Eventname, 
            count(Eventname) as 'Counts'
INTO        #TEMPTABLE                                                                                
FROM        tblevent
where       Eventname like 'A%'
Group by    Eventname
order by    count(Eventname)

Aquí usando la cláusula into la tabla se crea directamente

 -1
Author: linette J Sebastian,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-05-10 09:48:02