Temporary tables and SQL Server

When dealing with SQL Server and tables, sometimes you want to move data, make changes to a table just to see what happens and not affect your existing table structure. This might seem like a taboo thing, or part of the SQL Server Black Magic, but it is fairly simple to do this with temporary tables.

There are two types of temporary tables in Microsoft SQL Server:

• Local - these tables visible only in the current session, and they are created like a normal table but with a # in from of the table name
• Global - these temporary tables are visible to all sessions, and they are created like a normal table but with a ## in from of the table name

Here are a few rules regarding temporary tables:

• Creating temporary tables using the CREATE TABLE syntax supports constraint definitions, except for FOREIGN KEY constraints
• Temporary tables cannot be referenced in FOREIGN KEY constraints
• All local temporary temporary tables are dropped automatically at the end of the current session
• A local temporary table created in a stored procedure is dropped when the stored procedure is finished
• A local temporary table can be referenced by any nested stored procedures called by the stored procedure
• This means that if there is stored procedure "A", "B", and "C" and a temporary table is created in B, and stored procedure C is called with in B, and B is called by A, the temporary table can be referenced in both B and C, but not A.
• Local temporary table names cannot exceed 116 characters

To create a temporary table, we use the normal CREATE TABLE syntax, except we add a "#" right before the table name. Let's create a temporary table named "TempUsers"

1. CREATE TABLE #TempUsers
2. (
3.     firstName VARCHAR(30)
4.     , lastName VARCHAR(30)
5.     , nickName VARCHAR(30)
6. );

As you can see, it looks almost exactly the same as creating a normal table, except for the use of the "#". And we can pretty much do everything else with the temporary table (except using foreign key constraints, of course). For example, we can insert records into it:

1. INSERT INTO #TempUsers
2. (
3.     firstName
4.     , lastName
5.     , nickName
6. )
7. VALUES
8. (
9.     'John'
10.     , 'Doe'
11.     , 'Jimmy'
12. );

And if we did a select all on our table we can actually see the data entered with our insert statement:

1. SELECT * FROM #TempUsers;

Output:

firstName	lastName	nickName
John	Doe	Jimmy

And, if you want the overly complicated MSDN description of everything there is on temporary tables in Microsoft SQL Server, click here.