SQL Issue: SELECT INTO #TempTable

When writing queries, we often require an intermediate table to store results which will be used later in the query. One of the more common ways to achieve this is with a temporary table.

SELECT  Column1
        , Column2
INTO    #MyNewTable
FROM    Table1

When I see these in a code review, I usually ask if the temp table can be replaced with a table variable or a common table expression. Both of these tend to have better performance for most queries. Also, because they can only be used in the current query, there's less risk of side effects which make code difficult to maintain.

So if this was the original query.

SELECT Column1
       , Column2
INTO   #MyTemp
FROM   Example
WHERE  Column3 = 0

SELECT *
FROM   #MyTemp

DROP TABLE #MyTemp

The alternative using table variables would be.

DECLARE @tableVar TABLE (
    Col1 int,
    Col2 varchar(50)
)

INSERT INTO @tableVar
SELECT Column1
       , Column2
FROM   Example
WHERE  Column3 = 0

SELECT *
FROM   @tableVar

And this is the CTE version.

WITH 
  cteVar
  AS (
    SELECT Column1
           , Column2
    FROM   Example
    WHERE  Column3 = 0
  )

SELECT 
  *
FROM cteVar

These changes, for the most part, do not impact the functionality of the query. They're proposed to increase performance, readability, maintainability, etc. So make sure you test and review your changes with others.