SQL Temporary Tables: Syntax, Types, and Usage

Introduction

Database managers and developers use temporary tables, also referred to as temp tables, frequently. They are stored in the tempDB database and work just like regular tables, allowing you to pick, add, and remove data as needed. If they are created during a stored procedure, they will be eliminated after the process is complete.

What is a Temporary Table in SQL Server?

A database table that is only momentarily present on the database server is referred to as a temporary table in SQL. A temporary table keeps a portion of a regular table's data for a predetermined period of time.

When you need to consistently interact with only a tiny portion of the numerous records in a table, temporary tables come in handy. In some cases, you can filter the data once and save it in a temporary table instead of filtering the data multiple times to get the subset.

Permanent tables and temporary tables are nearly identical. When the final connection to the query window that generated the table is closed, they are generated in TempDB and immediately removed. Interim results can be processed and stored using Temporary Tables. When we need to store temporary data, we use temporary tables.

How to Create a Temporary SQL Table?

CREATE TABLE #tmpEmp
(
	Id INT,
	Name NVARCHAR(50),
	Rank INT
)

Types of Temporary Tables in SQL

There are a couple of temporary tables in SQL.

1. Local Temporary Tables
2. Global Temporary Tables

Local Temporary Tables

TempDB contains local temporary table storage. They are destroyed automatically at the conclusion of the operation or session, and they are only available to the one who created them.

For instance, after a local temporary table called #tmpEmp is created, the user's ability to manipulate the table is limited until the query window's final connection is closed. They can be recognized by the prefix #, such as #table name, and the same temporary table can be created with the same name in many windows.

A local temporary table can be created with the CREATE TABLE command, where the table name is prefixed with a single number sign (#table name).

Syntax

The following is the syntax in SQL Server (Transact-SQL) for making a LOCAL TEMPORARY TABLE.

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

Let’s insert some data entry in a temporary table.

INSERT INTO #tmpEmp ([Name], [Rank])
VALUES ('Jaimin Shethiya', 196)

Let’s check the results, whether it’s stored or not.

SELECT Id, [Name], [Rank] FROM #tmpEmp

Where do I store the temporary table on the SQL Server?

Another way to create a temporary table in SQL is with the SELECT INTO statement. For the illustration, go through the following things.

SELECT Id, [Name], [Rank]
INTO #tmpEmp1
FROM #tmpEmp

Global Temporary Table in SQL Server

Additionally, they are kept in tempDB. These tables belong to the category of transient tables that are concurrently accessible to all users and sessions. When the final session using the temporary table concludes, they are automatically removed. These tables do not exist in the system catalogs and are not persistent.

A global temporary table is created with the CREATE TABLE command, and the table name is preceded by a double number sign (##table name).

Syntax

The following is the syntax in SQL Server (Transact-SQL) for making a global temporary table.

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

Create a global temporary table.

CREATE TABLE ##tmpEmployee
(
	Id INT NOT NULL IDENTITY (1, 1),
	[Name] NVARCHAR(50),
	[Rank] INT
)

Let’s insert some data entry in a global temporary table.

INSERT INTO ##tmpEmployee ([Name], [Rank])
VALUES ('Jaimin Shethiya', 196)
, ('Roshni Shethiya', 1211)
, ('Dwisha Shethiya', 1250)
, ('Dwiti Shethiya', 1280)

Let’s check the results, whether it’s stored or not.

SELECT Id, [Name], [Rank] FROM ##tmpEmployee

Where do I store the temporary table on the SQL Server?

Delete Temporary and Global Temporary SQL Table

When possible, we should directly remove temporary tables rather than waiting for them to be deleted automatically when the connection is closed. in order to expeditiously release the temp resources.

Syntax

DROP TABLE TableName

As an illustration,

DROP TABLE #tmpEmp, #tmpEmp1, ##tmpEmployee

Let's check in the Temporary Tables from SQL Server.

After executing the DROP statement on the SQL side, the temporary tables are deleted from the Temporary tables from tempDB.

Differences between Temporary and Permanent Tables in SQL
 

Basis	Temporary Table	Permanent Table
Data persistence	Data in the temporary table is only kept for the duration of the current session.	Data is preserved indefinitely in the permanent table.
Storage allocation	Usually, memory or a temporary storage area are set aside for temporary storage.	Either a database or a disk house permanent storage.
Indexes and constraints	Constraints and indexes are typically transient and disappear along with the table.	It may be subject to triggers, constraints, and indexes.
Naming convention	Its names frequently include a keyword or special character prefixed to them.	Special characters or keyword prefixes are absent from permanent table names.
Data retention	When a session or connection ends, data automatically deletes.	Data remains in the table unless it is deleted or changed on purpose.
Transactional properties	The level of transactionality in database systems differs.	They facilitate ACID properties and take part in transactions.
Lifespan	It is only present for the current connection or session.	Even if the connection or session is ended, it still exists.
Accessibility	Only the session or connection that generated them can access them.	All users and connections with the necessary privileges can access them.

Summary

Numerous capabilities in Temporary Tables let you manage and save intermediate results.

Temporary tables can be a highly useful tool for storing temporary data in certain situations.

The primary concept of temporary tables is that they are eliminated at the conclusion of the current client session.

We learned the new technique and evolved together.

Happy coding!