SQL Server Local Temp Table Example

By: Rick Dobson

Overview

Temp tables are temporary in the sense that they exist for as long as the database connection which created and populated them exists.  After the connection is closed, the temp table drops from SQL Server.  You can also explicitly drop a temp table even when the connection remains open (for example, when the values in the temp table are no longer needed). 

There are two varieties of temp tables. 

• Local temp tables are only accessible from their creation context, such as the connection. 
• Global temp tables are accessible from other connection contexts.  (covered in the next section)

Both local and global temp tables reside in the tempdb database.

Creating and Populating Local SQL Server Temp Tables

A local temp table name begins with a single # sign.  Although you can create a local temp table from any database context, a local temp table always resides in the tempdb database.  Because a local temp table is a database table, you must drop any prior version of a local temp table before you can create and populate a fresh version of a local temp table.

You can create and populate a local temp table just like any other database table.  For example, you can use a SELECT statement with an INTO clause.  All other SELECT statement clauses besides the INTO clause define a result set for populating a local temp table.  The select list items specify columns for the local temp table.  The INTO clause argument specifies the name of the local temporary table.

The following script indicates some syntax for creating three local temp tables.

• The USE statement indicates the code operates in the context of the Temporary_Data_Stores_Tutorial database.  Several sections of this report use this database.  In the following T-SQL script, the name of the database is not critical to the operation of the code.  Therefore, you can use any other database that you prefer.
• Next, three try…catch blocks drop prior local temporary table versions if they exist with the names of #married_male, #married_female, and #not_married.
• Each of the three local temp tables are based on subsets from the Employee table of the HumanResources schema in the sample AdventureWorks2014 database.  The subsets are specified by setting(s) in the where clause for the select statement creating and populating each local temp table.
• The INTO clause within each SELECT statement designates the local temp table name for the result set developed by the rest of the SELECT statement syntax.

USE [Temporary_Data_Stores_Tutorial]
GO

-- drop local temp tables
BEGIN TRY
  DROP TABLE #married_male
END TRY
BEGIN CATCH
  PRINT '#married_male not available to drop'
END CATCH

BEGIN TRY
  DROP TABLE #married_female
END TRY
BEGIN CATCH
  PRINT '#married_female not available to drop'
END CATCH

BEGIN TRY
  DROP TABLE #not_married
END TRY
BEGIN CATCH
  PRINT '#not_married not available to drop'
END CATCH

-- populate local temp table for married, male employees
SELECT
  [BusinessEntityID] INTO #married_male
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] = 'M'
AND gender = 'M'

-- populate local temp table for married, female employees
SELECT
  [BusinessEntityID] INTO #married_female
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] = 'M'
AND gender = 'F'

-- populate local temp table for not married employees
SELECT
  [BusinessEntityID] INTO #not_married
FROM [AdventureWorks2014].[HumanResources].[Employee]
WHERE [MaritalStatus] != 'M'

The following screen shot displays the contents of the Messages tab from SSMS.  There is no Results tab because the SELECT statements create and populate local temporary tables instead of populate result sets that can be displayed.  The code generates three result sets for creating and populating local temp tables.  The script creates three result sets with row counts of 97, 49, and 144 rows each.  The sum of these row counts equals the total number of rows in the Employee table within the HumanResources schema of the AdventureWorks2014 database.

The preceding script works with three databases.

• The Use statement designates the Temporary_Data_Stores_Tutorial database.  This is the default database for the operation of the code in the script.
• A link at the end of this section designates one place for downloading the AdventureWorks2014 database along with releases of the AdventureWorks databases for other versions of SQL Server besides SQL Server 2014.  The AdventureWorks2014 database is explicitly relevant to the preceding script because the code uses a three-part name to designate a source table name in the FROM clause.  Without the database name part of the three-part name in the FROM clause, the code would default to searching the HumanResources schema for the Employee table in the Temporary_Data_Stores_Tutorial database.
• The third database implicitly referenced in the preceding script is the tempdb database.  Any table whose table name begins with a # symbol resides in the tempdb as a local temp table for the current database connection.