Temporary Tables
Temporary tables are tables that exist temporarily on the SQL Server.
The temporary tables are useful for storing the immediate result sets that are accessed multiple times.
Creating temporary table
SQL Server provided two ways to create temporary tables via SELECT INTO and CREATE TABLE statements.
Create temporary tables using SELECT INTO statement
Here we created a temporary table named #products with three columns derived from the select list of the SELECT statement.
The statement created the temporary table and populated data from the 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 Databases > System Databases > tempdb > Temporary Tables
As you can see clearly from the picture, the temporary table also consists of a sequence of numbers as a postfix. This is a unique identifier for the temporary table. Because multiple database connections can create temporary tables with the same name, SQL Server automatically appends this unique number at the end of the temporary table name to differentiate between the temporary tables.
Create temporary tables using CREATE TABLE statement
This can be a Local Temp Table (Accessable in current session only) or Global Temp Table
A. Local Temp Table (Accessable in current session only)
This statement has the same syntax as creating a regular table. However, the name of the temporary table starts with a hash symbol (#)
you can see same in Databases > System Databases > tempdb > Temporary Tables
This get automatically removed as and when we close that Query Window/ Session
Insert data into above temporary table as a regular table
Query data against above temporary table within the current session
However, if you open another connection (New Query Window) and try the query above query, you will get the following error:
Invalid object name '#Company'.
B. Global temporary tables (Accessable accross sessions / connections / query window)
Sometimes, you may want to create a temporary table that is accessible across connections. In this case, you can use global temporary tables.
the name of a global temporary table starts with a double hash symbol (##).
Insert data into Global Temporary table
Query a Global Temporary table
Dropping temporary tables
Automatic removal
SQL Server drops a temporary table automatically when you close the connection that created it.
SQL Server drops a global temporary table once the connection that created it closed and the queries against this table from other connections completes.
Manual Deletion
We can manually remove the temporary table by using the DROP TABLE statement:
