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 CalendarAS (SELECT /*...*/)Insert Into #TempSelect 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 Calendarwhere (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 #TempEnd

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.

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 DATAInsert Into #MyTempTableSelect 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 :)