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

Detailed guide and code snippets

Though the initial answer is straightforward, there are many nuances and subtleties you can grasp to refine your temp table wizardry. Let's explore some of them.

Building a CTE, the building block of temp tables

Defining your CTE with precision becomes necessary especially when dealing with vast data. Filtering data to select only the columns and rows you require can streamline the process.

;WITH WeeklyData AS (
  SELECT * FROM OriginalTable WHERE DateColumn >= GETDATE() - 7
) -- "One week ago, we looked back to the future."
SELECT * INTO #TempTable FROM WeeklyData; -- Beam it up, Scotty!


This piece of code sifts out data from only the last week, equipping you to work with a subset of your total dataset.

One ingredient at a time, please!

Despite the temptation to lump everything together, mindful selection of columns in SELECT INTO is advised. Optimizing column selection can greatly enhance performance.

Post-feasting clean-up (aka dropping your temp table)

A clean coder is a respectable coder. To achieve that, you must remember to drop your temp tables. A simple DROP TABLE #TempTable will suffice. While SQL Server automatically drops temp tables at the end of the session, it's a good habit to manually drop them after use.

Unfolding the magic of recursion in CTE

Recursion comes to rescue when your SQL business needs a recursive solution, like a list of dates. Recursion can be performed within your CTE. Just be sure to add OPTION (MAXRECURSION 0) if your data exceeds the 100 recursion limit.

;WITH DateSeries AS (
    SELECT CAST(GETDATE() AS DATE) AS DateValue
    UNION ALL
    SELECT DATEADD(DAY, 1, DateValue) FROM DateSeries WHERE DateValue < DATEADD(DAY, 10, GETDATE())
) -- "It ain't over till it's over."
SELECT * INTO #DateList FROM DateSeries OPTION (MAXRECURSION 0); -- "Time-travel mode activated!"


Here we've created a stellar temp table #DateList stocked with 10 days of future dates starting from today.

Value of meaningful naming conventions and prefixed columns

Sprinkling your entries with aliases can significantly improve readability as well as maintainability of your CTEs and temp tables.

;WITH SalesData AS (
    SELECT OrderID, OrderDate, TotalAmount FROM Orders WHERE OrderDate >= @StartDate
) -- "Filtered through the sands of time (and order dates)."
SELECT sd.OrderID, sd.OrderDate, sd.TotalAmount INTO #FilteredSales FROM SalesData AS sd; -- "Sales Rep: What filter? SQL: TEMP_Table."


Optimal usage of SQL resources

If the same result can be accomplished with a subquery or a table variable and your temp table isn't required, consider utilizing those to conserve resources. It is essential to test and benchmark every alternative reserving the temp table magic for the heavy-duty stuff.