Temporary Table in SQL

A temporary table in SQL is a special table used to store data temporarily during query execution. It helps hold intermediate results without affecting permanent tables.

• Stored in the system’s temporary database (like TempDB in SQL Server).
• Automatically deleted when the session or transaction ends.
• Useful for calculations or data processing without changing permanent data.

Syntax:

To Create a Temporary Table

CREATE TABLE EmpDetails (id INT, name VARCHAR(25))  

To Insert Values Into Temporary Table

INSERT INTO EmpDetails VALUES (01, 'James'), (02, 'Mike') 

To Select Values from the Temporary Table

SELECT * FROM EmpDetails 

Output:

Types of Temporary Tables in SQL

There are 2 types of Temporary Tables:

1. Local Temporary Table

A Local Temporary Table in SQL is a table that exists only for the duration of the session or connection that created it. It is used to store temporary data for intermediate processing and is automatically deleted when the session ends.

• In SQL Server, it is created using a single symbol (e.g., TempTable).
• Only accessible within the session or procedure that created it.
• Ideal for storing temporary results during complex queries or stored procedures.

Query:

CREATE PROCEDURE ProcTemp
AS
BEGIN
    CREATE TABLE EmpDetails (
        EmpID INT,
        EmpName VARCHAR(50)
    );
INSERT INTO EmpDetails (EmpID, EmpName)
    VALUES (1, 'Emilly'), (2, 'Steve');

SELECT * FROM EmpDetails;
END;
GO

EXEC ProcTemp;

Output:

2. Global Temporary Table

A Global Temporary Table in SQL is a temporary table that can be accessed by all sessions or users until the session that created it is closed. It is useful when multiple users need to share temporary data during execution.

• Created using a double prefix (e.g., TempTable).
• Accessible by all sessions after creation.
• Automatically dropped when the session that created it ends and no other sessions are using it.

Query:

CREATE TABLE EmpDetails (id INT, name VARCHAR(25)) 

Local Vs Global Temporary Tables

Here are the detailed comparison between Local and Global Temporary Tables:

When to Use Temporary Tables

Temporary tables are useful for storing short-lived data needed during query execution or processing.

Use Local Temporary Tables

Used to store temporary data for the current session only.

• Complex Queries: When you're breaking down a large query into smaller, manageable parts.
• Data Transformation: Store intermediate results while performing calculations.
• Procedure Debugging: Temporary data can be useful for testing or debugging stored procedures.

Use Global Temporary Tables

Used to store temporary data that can be shared across multiple sessions.

• Sharing Data: If you need to share temporary results between multiple sessions.
• Multi-user Applications: When multiple users need access to the same intermediate data.