How SQL query result insert in temp table?

A Closer Look at Temp Tables

Validate before you create

Before you cast SELECT INTO spell, it's vital to ensure your targeted temp table does not already exist to avoid any mid-air collisions. Here’s how you can perform this magic:

-- "I solemnly swear that I am up to no good."
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;

-- "Mischief managed!"
SELECT * INTO #TempTable FROM SourceTable WHERE Condition;


Adding dynamite to dynamic SQL

Working with dynamic SQL can be explosively good with temp tables. This approach includes safe handling routines such as checking table existence and conditional table dropping or creation.

-- "Once upon a midnight dreary, while I coded, weak and weary..."
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'IF OBJECT_ID(''tempdb..#TempTable'') IS NOT NULL DROP TABLE #TempTable; 
             SELECT Col1, Col2 INTO #TempTable FROM SourceTable WHERE Condition';
EXEC sp_executesql @sql;


MySQL: A different way to the same destination

In MySQL, you can manifest a temp table from the result set using the CREATE TABLE ... AS SELECT charm:

-- "Not all who wander are lost... but this might help if you are."
CREATE TEMPORARY TABLE IF NOT EXISTS TempTable AS
SELECT Col1, Col2 FROM SourceTable WHERE Condition;


Sorting your collectibles

If you fancy a sorted data collection while storing in your temp table, a pre-emptive ORDER BY clause before the INTO can do the trick:

-- Could've been sorted later, but why procrastinate? 
SELECT Col1, Col2 
INTO #TempTable 
FROM SourceTable 
WHERE Condition 
ORDER BY Col1, Col2; 


Practical insights and cautions

The Twin's Dilemma: Temp Table or Table Variable?

While choosing between temp tables and table variables, factors like scope, performance, and indexing are essential. Temp tables excel with large data and complex operations, while table variables shine in smaller tasks.

Rollback? Not with my Temp Table!

SQL Server treats temp tables just like other kids in the park. Rollbacks will revert changes made to temp tables. To keep the changes, commit the transaction:

-- "Here's Johnny!"
BEGIN TRANSACTION;
INSERT INTO #TempTable (Col) VALUES ('Data');
-- Phantom insert operations...
ROLLBACK; -- Redrum! Redrum! (Your changes to the temp table are gone!)


Indexing for the Win

Indexing can be a game-changer for temp tables when it comes to boosting query performance. However, choose wisely to strike a perfect balance between speed and the cost of maintaining those indexes:

-- "May the Index be with you."
CREATE UNIQUE INDEX idx_column1 ON #TempTable (Column1);
