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   lt   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  gt   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

User · Accepted Answer

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  gt   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

User · Answer

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

User · Answer

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     lt ---- INTO goes here           FROM   Calendar WHERE    PlannedDate  gt   Getdate            AND       EventEnumDays       LIKE        Cast Datepart dw  PlannedDate  AS CHAR 1                  OR EventEnumDays IS NULL ORDER  BY EventID            PlannedDate OPTION  maxrecursion 0

User · Answer

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

User · Answer

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

User · Answer

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

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

Sample DDL

Examples related to sql

Examples related to sql-server

Examples related to common-table-expression