Temporary Table in SQL

Published : 02 January 2025
Updated : 02 January 2025

137 Views

As the name describes , a temporary table is a table created to store temporary data in the database. You can perform SQL operations similar to the permanent table, such as CREATE, UPDATE, DELETE, INSERT, JOIN and more. Temporary tables are automatically deleted when the current client session is terminated. If needed, you can also explicitly delete a temporary table within the session.

Creating Temporary Tables in MySQL

To create temporary tables in MySQL, We follow the same table creating syntax as in a regular table. Use the CREATE TEMPORARY TABLE statement for creating a temporary table.

CREATE TEMPORARY TABLE TempTableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...............................
);

Key Points

Temporary Table is visible only to the user session that created them.
They are automatically dropped at the end of the session.
A temporary table can have the same name as permanent tables in the same database (no name conflict since scope is session-specific).

Let's Understand with a Example

Create a Temporary table Staffs with some columns and constraint and insert records into them.

CREATE TEMPORARY TABLE Staffs (
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Mobile varchar(20) Unique key,
    Salary DECIMAL(10, 2)
);

INSERT INTO Staffs VALUES 
(1, 'Ram',7654345654,89000.0),
(2, 'Gopal',5654345654,99000.0);

Now, when you run show table command, your temporary table will not be in table list because they are session-specific and not stored in the database's global schema.

To Verify this is your temporary table is created or not, run SELECT query on temporary table

SELECT * FROM Staffs;

Id	Name	Mobile	Salary
1	Ram	7654345654	89000.00
2	Gopal	5654345654	99000.00

Dropping Temporary Tables in MySQL

Although temporary tables are automatically deleted at the session ends, MySQL provide provision to manually delete a temporary table within a session using the below command

DROP TEMPORARY TABLE TempTableName;

Example

DROP TEMPORARY TABLE Staffs;

Verification

After running drop query, when you run SELECT query you will get error

SELECT * FROM Staffs;

Error Code: 1146. Table 'Staffs' doesn't exist 0.000 sec

Temporary Tables in SQL Server

SQL Server offers two types of temporary tables

Local Temporary Tables

Local Temporary tables are session-specific and can only be accessed by the session that created them and automatically deleted when the session ends or the connection is closed. They are created using a single # prefix in the table name

Syntax

CREATE TABLE #TempTableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...............................
);

Example

CREATE TABLE #Staffs(
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Mobile varchar(20) Unique key,
    Salary DECIMAL(10, 2)
);

Global Temporary Tables

Global Temporary Tables are accessible by all sessions and connections and are automatically deleted when the session that created it is closed and no other active sessions are referencing it.

They are created using a single ## prefix in the table name

CREATE TABLE ##TempTableName (
    Column1 DataType [Constraints],
    Column2 DataType [Constraints],
    ...............................
);

Example

CREATE TABLE ##Staffs(
    Id INT PRIMARY KEY,
    Name VARCHAR(50),
    Mobile varchar(20) Unique key,
    Salary DECIMAL(10, 2)
);

Dropping Temporary Tables in SQL Server

DROP TABLE #TempTableName;
DROP TABLE ##TempTableName;

Key Points

Temporary tables will be automatically deleted once the current client session is terminated.
You can also be explicitly deleted (within session) if the users decide to drop them manually.
Since all the temporary tables will be removed when the current session is closed, if you log out of the MySQL session and then issue a SELECT command, you will not find temporary table in the database.
Temporary tables support indexes, which can improve performance for larger datasets.
It is useful in situations where you need to perform calculations or data transformations without changing the permanent database structure.

Frequently Asked Questions (FAQs)

1. What is a temporary table in SQL?

A temporary table is a special type of table used to store data only for a short duration, typically within a single database session or transaction. It behaves like a normal table you can insert, update, delete and query data but it is automatically removed once the session ends or the connection is closed.

2. Why are temporary tables used in real-world applications?

Temporary tables are widely used to store intermediate results, perform complex calculations, simplify large queries and process data step by step. They help improve query readability, reduce repeated computations, and allow developers to work with temporary datasets without affecting permanent tables or production data.

3. Are temporary tables visible to other users or sessions?

No. Temporary tables are session-specific. A temporary table created in one session cannot be accessed by another session, even if it has the same name. This isolation ensures data safety and prevents conflicts when multiple users are working simultaneously.

4. Can a temporary table have the same name as a permanent table?

Yes. Temporary tables can share the same name as permanent tables because they exist only within the session scope. When a temporary table name matches a permanent table, SQL prioritizes the temporary table within that session, avoiding naming conflicts.

5. What happens to temporary tables when the session ends?

Temporary tables are automatically dropped when the session or connection ends. This cleanup happens without any manual action, ensuring temporary data does not remain in the database longer than necessary and does not consume storage unnecessarily.

6. Can I manually drop a temporary table before the session ends?

Yes. You can explicitly drop a temporary table using the DROP TEMPORARY TABLE command in MySQL or DROP TABLE in SQL Server. This is useful when the temporary table is no longer needed and you want to free up resources within the same session.

7. What is the difference between temporary tables in MySQL and SQL Server?

In MySQL, temporary tables are created using CREATE TEMPORARY TABLE and are always session-specific. In SQL Server, temporary tables come in two types: local (#TempTable) and global (##TempTable). Local temporary tables are session-specific, while global temporary tables can be accessed by multiple sessions.

8. What are local temporary tables in SQL Server?

Local temporary tables in SQL Server are created using a single # prefix. They are visible only to the session that created them and are automatically deleted when that session ends. They are commonly used for session-level data processing and intermediate calculations.

9. What are global temporary tables in SQL Server?

Global temporary tables use a ## prefix and can be accessed by multiple sessions. They are deleted only when the session that created them ends and no other sessions are using them. These are useful for shared temporary data but should be used cautiously.

10. Do temporary tables support indexes and constraints?

Yes. Temporary tables support indexes, primary keys, unique constraints and other standard table features. This makes them suitable for handling larger temporary datasets where performance and data integrity are important during intermediate processing.

11. Can I join temporary tables with permanent tables?

Absolutely. Temporary tables can be joined with permanent tables just like regular tables. This is a common use case when processing filtered or transformed data temporarily before combining it with persistent data for reporting or analysis.

12. Are temporary tables stored in memory or disk?

It depends on the database system and table size. Small temporary tables may be stored in memory, while larger ones may spill to disk. Database engines automatically manage this behavior to balance performance and resource usage.

13. Are temporary tables safe to use in production environments?

Yes, when used correctly. Temporary tables are safe because they do not modify permanent data. However, excessive use or very large temporary tables can impact performance. It’s important to design them efficiently and clean them up when no longer needed.

14. How are temporary tables different from subqueries or CTEs?

Temporary tables physically store data for reuse across multiple queries, while subqueries and CTEs are logical query constructs. Temporary tables are better when the same intermediate data is reused multiple times, whereas CTEs are ideal for simpler, one-time transformations.

15. Are temporary tables a common SQL interview topic?

Yes. Temporary tables are frequently discussed in SQL interviews to test understanding of session scope, performance and data isolation. Interviewers often ask candidates to compare temporary tables with CTEs or table variables and explain when to use each.