Cry How to...

Create a temporary table


Creating a temporary table is virtually the same as creating a normal table. The main exception is the naming of the table. A hash ('#') character as the first character in the table name denotes that it is a temporary table.

There are two types of temporary tables, local and global.

A local temporary table has a single hash ('#') at the start of its name. A local temporary table is visible only to the user who created it and is destroyed automatically when that user disconnects.

A global temporary table is denoted by a name starting with two hashes (i.e. '##'). A global temporary table is visible to all users and is deleted automatically when the last user who has referenced the table disconnects.

An example of creating a local temporary table:

create table #foo
(
    CarIndex  smallint,
    CarType   varchar(20)
)

An example of creating a global temporary table:

create table ##baz
(
    CarIndex  smallint,
    CarType   varchar(20)
)


These notes have been tested against SQL Server 7 and are believed to be correct for later versions as well.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.