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

1. Local Temporary Table

A Local Temporary Table is a temporary table used to store intermediate data during query execution or processing. It is created using a single # prefix (e.g., #TempTable).

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 is a temporary table used to store temporary data that can be shared across multiple operations. It is created using a double ## prefix (e.g., ##TempTable).

Query:

CREATE TABLE #EmpDetails (
    EmpID INT,
    EmpName VARCHAR(50)
);

Local vs. Global Temporary Tables