Pressing the Red Button

Here are three methods of creating and working with temporary tables in Microsoft SQL Server:

Method 1: Select Into
This is a quick and dirty method to create a temporary table. Compared to the other methods, you do not have to define the column names. Just add the ‘into #temptablename’ at the end of the columns you want selected.

select id, code 
into #mytemptable
from sourcetable

--view the data
select * from #mytemptable

Method 2: Create Table
Use this method if you want more control of the definition of the table and column types

CREATE TABLE #mytemptable (
dataid int,
description varchar(30) )

-- insert some data into it
insert into #mytemptable
select id, code from sourcetable

-- view the data
select * from #mytemptable

-- drop the table if you want to
drop table #mytemptable

Method 3: Table Variable
This method is useful for programming stored procedures and user functions. Once you define the table, you can take advantage of passing table variables back and forth between code.

DECLARE @mytemptable TABLE (
dataid int,
description varchar(30) )

-- insert some data into it
insert into @mytemptable
select id, code from sourcetable

-- view the data
select * from @mytemptable

How Long are Temporary Tables Good For:
Temporary tables are good for the length of your database session. If you have a query window in management studio, once you create a temporary table, it is available until you close that query window. You can also use the ‘drop table’ statement on it.

What are Temporary Tables Good For:
– If you are analyzing data, it helps to store the results of frequently or long running sql
– If you are doing complex sql or aggregation (like or data warehouses), it might make your process simpler. I would use table variables (method 3) in that case, for performance reasons.