Temporary Table in SQL
Learn via video course
FreeOverview
Temporary tables, often known as temp tables, are widely used by database administrators and developers. Temporary tables are kept in the tempDB database. They function similarly to the conventional tables in which you can select, insert, and delete data as per your requirements. If they're produced inside a stored procedure, they'll be destroyed once the procedure is finished.
Scope
We are going to cover the following topics in this tutorial:
- We will first discuss the basics of temporary tables and how to create, select and insert values to a temporary table.
- Then, with the help of examples, we will understand the types of temporary tables (i.e., local and global temporary tables).
- At last, we will see various methods of dropping a temporary table in SQL.
What is a Temporary Table in SQL?
- A temporary table in SQL is a database table that exists only temporarily on the database server. For a fixed amount of time, a temporary table maintains a subset of data from a normal table.
- Temporary tables are convenient when you have a large number of records in a table and need to interact with only a small subset of those records regularly. In such circumstances, rather than filtering the data repeatedly to obtain the subset, you can filter the data once and save it in a temporary table.
- Temporary Tables are almost the same as Permanent Tables. Temporary tables are produced in TempDB and automatically deleted when the last connection to the query window that created the table is terminated. We can use Temporary Tables to store and process intermediate results. Temporary tables come into use when we need to store temporary data.
To Create a Temporary Table:
To Insert Values to a Temporary Table:
To Select values from a temporary Table:
Output:
Example Here's an example of how to use a temporary table.
Output:
Note: When you perform the SHOW TABLES command, your temporary table will not appear in the list. If you log out of the MySQL session and then submit a SELECT command, you will discover that there is no data in the database. Even your temporary table will be gone.
Local Temporary Tables
Local temporary tables are the tables stored in tempDB. These types of tables are only accessible to the session that produced them. When the procedure or session ends, these tables are automatically destroyed.
For example, if we create a local temporary table named #my_table, the user can only perform functions on the table until the last connection to the query window is terminated. They are identified by the prefix #, for example, #table name, and the same temp table can be established in numerous windows with the same name.
The CREATE TABLE command is used to create a local temporary table with the table name preceded by a single number sign (#table name).
Syntax:
In SQL Server (Transact-SQL), the syntax for creating a LOCAL TEMPORARY TABLE is:
Parameters or Arguments
| Parameters | Description |
|---|---|
| table_name | It is the name of the local temporary table that you want to construct. The local temporary table's name begins with the character # |
| column1, column2, column3... column_n | The columns that you want to add to the local temporary table. A datatype must be assigned to each column. The column should be declared as either NULL or NOT NULL, and if this value is left blank, the database will assume NULL as the default. |
Example:
Let's have a look at how to make a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL).
This example will construct a LOCAL TEMPORARY TABLE in SQL Server called #employees with four columns.
| Column Name | Description |
|---|---|
| employee_id | The first column is called employee_id, and it's an INT datatype that can't have NULL values. |
| last_name | The second column, called last name, has a VARCHAR data type with a maximum length of 50 characters and cannot include NULL values. |
| first_name | The third column, first name, is a VARCHAR datatype that can consist of NULL values. |
| salary | Salary is the fourth column, and it is a MONEY data type that can contain NULL values. |
The employee_id column is the main key for the #employees table.
This #employees table is stored in tempDB, and SQL Server will destroy it when the SQL Server session is no longer needed.
Note: The names of LOCAL TEMPORARY TABLES begin with the character # (for example, #employees).
Global Temporary Tables
Global temporary tables are also stored in tempDB. Global temporary tables are one of the types of temporary tables that are accessible to all sessions and users simultaneously. They are automatically deleted when the last session that used the temporary table has ended. These tables are not persistent and do not reside in the system catalogs.
Note: They are denoted by the prefix ##, as in ##table name.
The CREATE TABLE command creates a global temporary table with the table name preceded by a double number sign (##table name).
Syntax:
In SQL Server (Transact-SQL), the syntax for creating a GLOBAL TEMPORARY TABLE is:
Parameters or Arguments
| Parameters | Description |
|---|---|
| table_name | It is the name of the global temporary table that you want to construct. The global temporary table's name begins with the double ## character. |
| column_1, column_2, column_3... column_n | The columns that you want to add to the global temporary table. A datatype must be assigned to each column. The column should be declared as either NULL or NOT NULL, and if this value is left blank, the database will assume NULL as the default. |
Example:
Let's have a look at how to make a GLOBAL TEMPORARY TABLE in SQL Server (Transact-SQL). This example will construct a GLOBAL TEMPORARY TABLE in SQL Server called ##suppliers_temp with three columns.
| Column Name | Description |
|---|---|
| supplier_id | The first column is called supplier_id, and it's a numeric datatype with a maximum length of 10 characters that can't have NULL values. |
| supplier_name | The second column, supplier name, is a Char data type with a maximum length of 50 characters and cannot include NULL values. |
| contact_name | The third column, contact name, is a Char datatype that can consist of NULL values. |
The following is the query used to create this table:
Dropping Temporary Tables
Using the OBJECT_ID Function to Check for the Existence of a Temporary Table
The OBJECT_ID function is used to acquire the database object's identification number. Because the OBJECT_ID function accepts the object's name as an input, it can be used to verify the existence of any object in a database.
The following query will check for the existence of the #LocalCustomer table in the tempdb database, and if it exists, it will be discarded.
For the local temporary tables:
For the global temporary tables:
Using the sys. tables Table to Check for the existence of a Temporary Table
In this approach, we will utilize sys. tables to check the existence of the temporary table because this table returns user tables in the relevant database.
For the local temporary tables:
For the global temporary tables:
As we can see, we check to see if the #LocalCustomer table exists in the tempDB database, and if it does, we must drop it. At this time, we should emphasize one point: the table name is searched with the LIKE operator, and the wildcard character has been placed at the end of the temp table name. As previously indicated, local temp tables are formed with a random suffix, therefore, the actual name is unknown.
Using the DROP TABLE IF EXISTS Statement
This is the final approach for dropping a temp table. MySQL deletes all temporary tables by default when your database connection is terminated. Still, if you wish to delete them in the meantime, use the DROP TABLE IF EXISTS statement. The DROP TABLE IF EXISTS statement checks for the existence of the table and drops it if it exists. We must emphasize one aspect regarding this statement: it is only applicable to SQL Server 2016 or later.
For the local temporary tables:
For the global temporary tables:
Check out this article to learn more about SQL Server.
Conclusion
- Temporary Tables offers a lot of features that allow you to store and handle intermediate results using the same selection, update, and join capabilities as standard SQL Server tables.
- In some circumstances, temporary tables could be very beneficial for storing temporary data. The most important thing to understand about temporary tables is that they are destroyed when the current client session ends.