Temporary Table in SQL

Published : 02 January 2025
Updated : 02 January 2025

131 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.