Temporary Tables
Learn how to create and use temporary tables.
We'll cover the following
Besides table variables, there are temporary tables we can utilize in our complex queries to hold intermediate data. Unlike table variables that exist only within the scope of a batch, temporary tables are accessible throughout the whole session when there’s a connection to the MS SQL Server. Temporary tables can be either local or global.
Temporary table types
There are two types of temporary tables in MS SQL Server.
Local temporary tables
Local temporary tables are accessible only from the session they are created in. Users or applications connected to the MS SQL Server are not able to access the temporary tables created in other sessions. They are automatically dropped when the session closes.
Syntax
Local temporary tables are created similarly to regular tables with one notable difference. We put #
in front of the table name:
CREATE TABLE #TempData
(
Id INT PRIMARY KEY IDENTITY(1, 1),
Info NVARCHAR(100) NOT NULL
);
To delete a local temporary table before the session is closed, we can use the DROP TABLE
command, just like we do with a regular table:
DROP TABLE #TempData;
Usage
Like table variables, local temporary tables can be used within stored procedures or other complex queries. They support all the commands we expect from a table:
Get hands-on with 1200+ tech skills courses.