Temporary Table in SQL
FREE
Temporary tables, often known as temp tables, are widely used by database administrators and developers. They are kept in the tempDB database and function similarly to 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.
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 many records in a table and need to regularly interact with only a small subset of those records. 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. They 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 are used when we need to store temporary data.
How to Create a Temporary SQL Table
In SQL, We can create a temporary table using the CREATE TABLE command along with the TEMPORARY or TEMP keyword.
Example:
Output:
Example Here's an example of how to use a temporary table.
Output:
| emp_name | emp_rate | emp_sal | yoe |
|---|---|---|---|
| ram | 10.25 | 9000 | 2 |
Note: Your temporary table will not appear in the list When you perform the SHOW TABLES command. 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.
Types of Temporary Tables in SQL
There are two major different types of temporary tables in sql:
- Local Temporary Tables
- Global Temporary Table
Local Temporary Table in SQL
Local temporary tables are stored in tempDB. They are only accessible to the session that produced them, and when the procedure or session ends, they 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 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 create a LOCAL TEMPORARY TABLE in SQL Server (Transact-SQL).
This example will construct a LOCAL TEMPORARY TABLE in SQL Server called #course with four columns.
| Column Name | Description |
|---|---|
| course_id | The first column is called course_id, and it's an INT datatype that can't have NULL values. |
| course_name | The second column, called course name, has a VARCHAR data type with a maximum length of 50 characters and cannot include NULL values. |
| first_name | The third column, faculty_name, is a VARCHAR datatype that can consist of NULL values. |
| cost | Cost is the fourth column, and it is a MONEY data type that can contain NULL values. |
The course_id column is the main key for the #course table.
This #course table is stored in tempDB, and the 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 Table in SQL
They are also stored in tempDB. These 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 ends. These tables are not persistent and do not reside in the system catalogs.
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_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. |
Example:
Let's 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:
Differences between Temporary and Permanent Tables in SQL
| Basis | Temporary Table | Permanent Table |
|---|---|---|
| Data persistence | In the temporary table, data is not persisted beyond the current session | In the permanent table, data is persisted permanently |
| Indexes and constraints | Indexes and constraints are usually temporary, dropped with the table. | It can have indexes, constraints, and triggers. |
| Storage allocation | Temporary storage is usually allocated in memory or a temporary storage space. | Permanent storage resides either on disk or in a database.. |
| Naming convention | It names are often prefixed with a special character or keyword | Permanent table names lack special character or keyword prefixes. |
| Data retention | Data auto-deletes upon session or connection end. | Data persists in the table until intentionally altered or removed. |
| Lifespan | It exists only for the current session or connection | It persists even after the session or connection is closed. |
| Transactional properties | Transactionality varies across database systems. | They participate in transactions and support ACID properties. |
| Accessibility | They are accessible only to the session or connection that created it | They are Accessible to all users and connections with suitable privileges. |
Conclusion
- Temporary Tables offers many features that allow you to store and handle intermediate results.
- 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.