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 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(
      emp_id INT,
      emp_name VARCHAR(50)
    );
INSERT INTO #empdetails (EmpID, EmpName)
    VALUES (1, 'Emilly'), (2, 'Steve');

SELECT * FROM #empdetails;
END;
GO

EXEC proc_temp ;

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 (
    emp_id INT,
   emp_name VARCHAR(50)
);

Local vs. Global Temporary Tables