SQL Server – Temporary Tables


To totally unlock this section you need to Log-in

It is very beneficial to store data in SQL Server temp tables rather than manipulate or work with permanent tables. Let’s say you want full DDL or DML access to a table, but don’t have it. You can use your existing read access to pull the data into a SQL Server temporary table and make adjustments from there.

Or you don’t have permissions to create a table in the existing database, you can create a SQL Server temp table that you can manipulate. Finally, you might be in a situation where you need the data to be visible only in the current session.

Temporary tables can be local or global.

  • A local table is visible only to the session that it is created from and is dropped automatically when the connection is closed. Other users cannot access the table, although they may create their own temporary with the same name. In such situations the two temporary tables are in no way linked.
  • Global temporary tables are available to all users and are automatically dropped when the last user accessing the table disconnects from the database. Although both types of table will be automatically dropped, it is common practice to delete them manually when no longer required.

Local Temporary Tables

Local Temporary Tables are distinct within modules and embedded SQL programs within SQL Server sessions. Local temporary tables are stored in tempdb and SQL Server automatically deletes these tables when they are no longer used.

If a local temporary table created in a stored procedure, it is dropped automatically when the stored procedure is finished. This means that this local temporary table can be referenced only by nested stored procedures.

The local temporary table cannot be referenced by the stored procedure or application that called the stored procedure that created the local temporary table.

For example, using CREATE TABLE approach, the syntax to create a local temporary tables in SQL Server (Transact-SQL) is:

CREATE TABLE #table_name
( 
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...
);

Global Temporary Tables

The name of the SQL Global temporary table starts with the double hash (“##”) symbol and stored in the tempdb. Global temp tables in SQL Server are like permanent tables, and they are available to all the users in that instance.

If all the user disconnects from their session, the SQL global temp tables will automatically delete.

Let us create a SQL global temporary table called GlobalTemp.

-- Creating Global Temp Table in SQL Server 
CREATE TABLE ##GlobalTemp
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](255) NULL,
	[LastName] [nvarchar](255) NULL,
	[Education] [nvarchar](255) NULL,
	[Occupation] [nvarchar](255) NULL,
	[YearlyIncome] [float] NULL,
	[Sales] [float] NULL
)

Global temporary table is automatically dropped when the session that created the table ends and the last active Transact-SQL statement (not session) referencing this table in other sessions ends.

The following is an example to insert a few samples or random records into the global temporary table in SQL Server that we created inside the tempdb using the INSERT Statement.

-- Inserting Values into Global SQL Temp Table
INSERT INTO ##GlobalTemp (
	    [FirstName], [LastName], [Education], [Occupation], [YearlyIncome], [Sales])
VALUES ('Tutorial', 'Gateway', 'Masters Degree', 'Teaching', 12000, 200)
      ,('Imran', 'Khan', 'Bachelors', 'Skilled Professional', 13900, 100)
      ,('Doe', 'Lara', 'Degree', 'Management', 25000, 60)
      ,('Ramesh', 'Kumar', 'Bachelors', 'Professional', 35400, 630)

Parameters or Arguments

  • table_name: The name of the local temporary table that you wish to create. The name of the local temporary table starts with the # character.
  • column1, column2: The columns that you wish to create in the local temporary table. Each column must have a datatype.

The column should either be defined as NULL or NOT NULL and if this value is left blank, the database assumes NULL as the default.

The name of local temporary tables are prefixed with the # character (ie: #employees).

Let's look at an example of how to create a local temporary table in SQL Server (Transact-SQL).

For example:

CREATE TABLE #example
( example_id INT PRIMARY KEY,
  trek1 VARCHAR(50) NOT NULL,
  trek2 VARCHAR(50),
  trek3 MONEY
);

This example would create a local temporary tables called #employees in SQL Server which has 4 columns.

  • The first column is called example_id which is created as an INT datatype and can not contain NULL values.
  • The second column is called trek1 which is a VARCHAR datatype (50 maximum characters in length) and also can not contain NULL values.
  • The third column is called trek2 which is a VARCHAR datatype but can contain NULL values.
  • The fourth column is called trek3 which is a MONEY datatype which can contain NULL values.
  • The primary key for the #employees table is the example_id column.
  • This #employees table is stored in tempdb and SQL Server will automatically delete this table when the SQL Server session no longer requires it.

SELECT INTO

Microsoft SQL Server provides two ways to create temporary tables: SELECT INTO and CREATE TABLE statements.

The first way to create a temporary table is to use the SELECT INTO statement as shown below:

SELECT 
    select_list
INTO 
    temporary_table
FROM 
    table_name
....

The name of the temporary table starts with a hash symbol (#). For example, the following statement creates a temporary table using the SELECT INTO statement:

SELECT
    product_name,
    list_price
INTO #trek_products --- temporary table
FROM
    production.products
WHERE
    brand_id = 9;

In this example, we created a temporary table named #trek_products with two columns derived from the select list of the SELECT statement. The statement created the temporary table and populated data from the production.products table into the temporary table.

Once you execute the statement, you can find the temporary table name created in the system database named tempdb, which can be accessed via the SQL Server Management Studio using the following path System Databases > tempdb > Temporary Tables.

CREATE TABLE

Creating a temporary table uses the same syntax used to create a standard table. To signify that the table is a local temporary table, a hash (or pound) symbol (#) is used as a prefix to the table name. The name may be up to 116 characters in length.

Add the following temporary table creation statement to the stored procedure code:

CREATE TABLE #CustomerValue
(
    CustomerNumber INT,
    TotalContractValue MONEY,
    EngineerCost MONEY,
    PartsCost MONEY,
    Profit MONEY
)

Populating the Initial Data

To initialise the data in the temporary table we will copy all of the customer numbers from the live data. All other columns in the will contain NULL values at this point. We can copy the customer numbers using an INSERT statement that sources its information from a query as follows:

INSERT INTO #CustomerValue (CustomerNumber)
SELECT CustomerNumber FROM Customers

Dropping Temporary Tables

Temporary tables are dropped automatically when the database connection or session in which they are created is closed. However, if want to delete them without closing the current session, you can use the DROP TABLE statement, as follow:

DROP TABLE ##table_name;

The above statement will delete the temporary table table_name from the database. After that, the original table_name base table becomes visible.

At the beginning of a SQL script we could even, for example, test if the temporary table already exist and then drop it if it is true. The following code is valid for SQL Server 2014 and below versions (but not only, also for above versions):

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
BEGIN 
    DROP TABLE #TempTable 
END

The following code instead, is valid only on SQL Server 2016 and above. In SQL Server 2016, Microsoft introduced DIY or DROP IF EXISTS functionality. By adding IF EXISTS to the drop statement, you can drop the object only when it exists in the database.

DROP TABLE IF EXISTS #TempTable
GO

List of all Temporary Tables created on SQL Server

The following two statements will give us a complete list of all available temporary tables on SQL Server instance:

select * from tempdb..sysobjects
--For SQL Server 2000 and later versions

select * from tempdb.sys.objects
--For SQL Server 2005 and later versions

Add Columns to Temporary Tables

Adding new temporary columns to a temporary table is also another useful modification usually used to manipulate table schema without altering the production table or to test some new trasnformation from other existing columns. The following approach can be used to do this:

ALTER TABLE #TEMP_TBL ADD new_col DECIMAL(18,4)
ALTER TABLE #TEMP_TBL ADD new_col NVARCHAR(max)

The following code creates a primary key on the ExampleId column:

ALTER TABLE #Ex_Temp ADD PRIMARY KEY (ExampleId)

The ALTER TABLE is called on the Ex_Temp table and the primary key is added on the ExampleId column.

And finally, here a simple approach to change only a single variable to create multiple columns into a temporary (not only) table:

DECLARE @ColName nvarchar(100)
DECLARE @SQL nvarchar(250)
SET @ColName='newColumn'
SET @SQL = 'ALTER TABLE #Mytemp ADD ['+ CAST(@ColName AS NVARCHAR(100)) +'] NVARCHAR(100) NULL'
EXEC(@SQL)