Three Ways to Create a Temp Table

Taking it back to SQL 101 today because I recently saw something that floored me. I’m a big fan of temp tables. I use ’em all over the place to stash intermediate results, break a dataset down into more manageable chunks, or even share them between procedures. And as it turns out, there’s more than one way to create them.

The Traditional Way

If you’re planning out your temp table usage, you’re probably creating them just like any other table, then populating them.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

create table #MyTempTable
(
    [number] int not NULL,
    [name] nvarchar(35) NULL
);

insert into #MyTempTable
    ([number],[name])
select [number], [name]
from master.dbo.spt_values;

The Quick Way

Contestant #2 is pretty easy to follow as well, but comes with a little baggage.

1
2
3

select [number], [name]
into #MyTempTable
from master.dbo.spt_values;

This is a bit of a shortcut. Rather than declaring our table & the field types before populating it, we’re creating the table on the fly based on the data types that come out of the select statement. If you’re using user-defined types, this can get a little muddy. But generally, this works OK. It still signals intent - I want to take the results of this query and drop them into a temp table.

The “Wait…what? Why!?” Way

The third way is kind of bonkers.

1
2
3
4
5
6
7
8
9

select 0 as [number], cast('' as nvarchar(35)) as [name]
into #MyTempTable
from sys.databases
where 0=1;

insert into #MyTempTable
    ([number],[name])
select [number], [name]
from master.dbo.spt_values;

This one really gums up the works for the person reading your code in the future (that person may be you!). At first, it looks like it’s selecting from a regular table into a temp table. But it’s doing a CAST(), and not actually selecting any of the fields on the table. But hey, that’s alright! Because thanks to where 0=1, it’ll never return any rows!

Does this create the temp table we want? Yes. Is the intent of the code clear? Not in my opinion. It’s creating an empty data set with the types that we want, but in a roundabout way, and referencing a table that may have nothing to do with the operation we’re performing. If you’re doing this, please leave a note.

Please read Jeff Moden’s excellent comment below which explains this reasoning behind and potential benefits of this method.

You may be thinking “I should omit the FROM and WHERE. After all, they’re just clutter here, right?” If you do, you aren’t creating an empty set anymore, you’re selecting those values and creating a one-record set!

It turns out that you can do it without the FROM and WHERE if you use TOP 0. But as above, it’s kind of unclear in my opinion.

select TOP 0 0 as [number], cast('' as nvarchar(35)) as [name]
into #MyTempTable

Yes, I Have a Preference

There are three (and a half?) ways to create a temp table presented here (and there’s probably more that I’m not aware of - dynamic SQL doesn’t count though). If I’m playing Kiss, Marry, Kill with them, I’m marrying the first one (CREATE TABLE), kissing the second one (SELECT INTO #TempTable), and throwing that last one into the volcano unless there’s a good reason for having it (again, please read Jeff’s comment below).