How to Create Temp Table with Select * into Temptable from Cte Query

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

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

Inserting the result of a with cte query into a Temp Table

Assuming this is for SQL Server : the CTE is good for only one statement - so you cannot have both a SELECT and an INSERT - just use the INSERT:

WITH cOldest AS
(
    SELECT 
       *, 
       ROW_NUMBER() OVER (PARTITION BY [MyKey] ORDER BY SomeColumn DESC) AS rnDOB 
    FROM MyTable
)
INSERT INTO #MyTempTable(Col1, Col2, ....., ColN)
  SELECT Col1, Col2, ...., ColN
  FROM cOldest C
  WHERE C.rnDOB = 1

This requires that the #MyTempTable already exists. If you want to create it with the SELECT - use this syntax:

WITH cOldest AS
(
 .....
)
SELECT c.*
INTO #MyTempTable
FROM cOldest c
WHERE C.rnDOB = 1

creating a temp table from a with table as CTE expression

A set of CTEs introduced by a WITH clause is valid for the single statement that follows the last CTE definition. Here, it seems you should just skip the bare SELECT and make the INSERT the following statement:

WITH  abcd
        AS (
              -- anchor
            SELECT   id
                    ,ParentID
                    ,CAST(id AS VARCHAR(100)) AS [Path]
                    ,0 as depth
            FROM    @tbl
            WHERE   ParentId = 0
            UNION ALL
              --recursive member
            SELECT  t.id
                   ,t.ParentID
                   ,CAST(a.[Path] + ',' + CAST( t.ID AS VARCHAR(100)) AS varchar(100)) AS [Path]
                   ,a.depth +1
            FROM    @tbl AS t
                    JOIN abcd AS a ON t.ParentId = a.id
           )
insert into #TMP (id,parent,branch,depth) select * from abcd

select * from #TMP

(I've added the select from #TMP so that we still get a result set returned to the client, albeit that the insert and select statements are now reversed).

Insert into a Temp Table in a CTE

Simply, you cannot use the INSERT function inside a CTE. Assuming "Final" was one of the other CTE's in the multi CTE script, just move the INSERT INTO #Clients outside the CTE script. You seemingly don't need the temp table, since you are using CTE's, the Clients CTE will be available temp table or no. I suggest getting rid of the temp table altogether and continuing with the CTE method you already have in place. You may need to post more of the script to get better scope of the question.

 ,Clients as
     (Select 
     Distinct
     HospMastID
     ,HospCode
     ,ClientID
     From
    Final)

How to insert CTE output to temp table in Oracle?

Just Replace the order for create table statement like below :

create table temp_recent_order as
with cte as (
select ORDER_ID,
       STATUS_ID,
       CALL_DATE,
       SHIP_DATE,
       UPDATE_USER_ID,
       UPDATE_TIMESTAMP,
       row_number() over(partition by ORDER_ID order by update_timestamp desc) as rowno
  from ORDER_HISTORY
 where ORDER_ID in (1001, 1002, 1003)
)
select * from cte where rowno=1;

Create a temp table from two tables, selecting the latest date before a specific date

First, learn to use proper JOIN syntax. Then you can use a correlated subquery to select the most recent date before your cutoff:

SELECT F.passenger_id, P.company_name, f.flight_number, f.destination
FROM FlightTable F JOIN
     PassengerTable P
     ON F.passenger_id = P.passenger_id  
WHERE f.flight_date = (SELECT MAX(f2.flight_date) 
                       FROM FlightTable f2
                       WHERE f2.passenger_id = f.passenger_id AND
                             f2.flight_date < '2012-12-28'
                      );

It is possible to use temp table in CTE?

The syntax of your query is incorrect. You cannot create and drop the #TEMP table within the CTE query.

BTW, CTE is not required on this case, given that all the info you need is on the #TEMP table. You can rewrite the query as follows:

SELECT CONVERT(VARCHAR(15), cast(Substring(unitts, 1, 8) AS DATE), 105) AS Data,
                Substring(UnitTS, 9, 2) + ':' + Substring(UnitTS, 11, 2)         AS EventTime,
                CASE
                  WHEN RdrHead = 'A' THEN 'OUT'
                  ELSE 'IN '
                END                                                              AS Reader,
                [RdrName],
                [CrdName],
                IDENTITY (int, 1, 1)                                             AS rn,
                UnitTS
         INTO   #TEMP --rn = row_number() over (order by Crdname,UnitTs)
         FROM   TandA.dbo.History
         WHERE  ( UnitNr = '3'
                  AND RdrNr IN ( '0', '2', '3' )
                   OR UnitNr = '4'
                      AND RdrNr IN( '1', '6' ) )
                AND Type = 'A'
                AND Sign = '+'
                AND Substring(unitts, 1, 8) >= @Start_Date
                AND Substring(unitts, 1, 8) <= @End_Date
                AND ( CrdName IN ( @mp )
                       OR @emp = 'all' )

                       ORDER BY rn;

SELECT o_out.CrdName,
       o_out.RdrName,
       o_out.Data,
       CASE
         WHEN o_in.EventTime IS NULL THEN 'Necunoscut'
         ELSE o_in.EventTime
       END In_Time,
       [Out_Time] = o_out.EventTime,
       CASE
         WHEN cast(datediff (s, o_in.EventTime, o_out.EventTime) AS INT) IS NULL THEN '0'
         ELSE cast(datediff (S, o_in.EventTime, o_out.EventTime) AS INT)
       END Duration
FROM   Ordered o_out
       LEFT JOIN #TEMP o_in
         ON o_in.rn = o_out.rn - 1
            AND o_in.Reader = 'in'
WHERE  o_out.Reader = 'out';

DROP TABLE #TEMP;

---

Related Topics