Temporary Tables
Learn how to create, use, query and remove temporary tables in SQL Server.
What are Temporary Tables?
Temporary tables are special tables used to store temporary data during SQL Server operations.
They are useful when we need to store intermediate results while executing complex queries, stored procedures or multiple SQL operations.
Temporary tables are created in tempdb and SQL Server automatically removes them when their scope ends.
Types of Temporary Tables
SQL Server mainly provides two types of temporary tables:
| Type | Symbol | Scope |
|---|---|---|
| Local Temporary Table |
#
|
Available only in the current session. |
| Global Temporary Table |
##
|
Available to multiple sessions until it is no longer being used. |
1. Local Temporary Table
A local temporary table is created using a single # symbol.
It is normally available only within the session in which it was created.
Syntax
CREATE TABLE #Employees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Department VARCHAR(100),
Salary DECIMAL(10,2)
);
Insert Data into Temporary Table
INSERT INTO #Employees
(
EmployeeId,
EmployeeName,
Department,
Salary
)
VALUES
(1, 'Amit', 'IT', 70000),
(2, 'Rahul', 'HR', 60000),
(3, 'Priya', 'IT', 80000),
(4, 'Neha', 'Finance', 65000);
The temporary table can be used like a normal table after it has been created.
Select Data from Temporary Table
SELECT *
FROM #Employees;
Output
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Amit | IT | 70000 |
| 2 | Rahul | HR | 60000 |
| 3 | Priya | IT | 80000 |
| 4 | Neha | Finance | 65000 |
Query Temporary Table
We can use filtering and sorting operations just like a normal table.
SELECT
EmployeeId,
EmployeeName,
Salary
FROM #Employees
WHERE Salary > 65000
ORDER BY Salary DESC;
Update Temporary Table
UPDATE #Employees
SET Salary = 75000
WHERE EmployeeId = 1;
Temporary tables support INSERT, UPDATE and DELETE operations like permanent tables.
Delete Data
DELETE FROM #Employees
WHERE EmployeeId = 4;
2. Global Temporary Table
A global temporary table is created using two ## symbols.
Global temporary tables can be accessed by multiple sessions while the table exists and is being used.
Example
CREATE TABLE ##GlobalEmployees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);
Drop Temporary Table
A temporary table can be explicitly removed using the DROP TABLE statement.
DROP TABLE #Employees;
For a global temporary table:
DROP TABLE ##GlobalEmployees;
Create Temporary Table using SELECT INTO
A temporary table can also be created directly from an existing table using SELECT INTO.
SELECT
EmployeeId,
EmployeeName,
Department,
Salary
INTO #ITEmployees
FROM Employees
WHERE Department = 'IT';
SQL Server creates the temporary table automatically based on the selected columns.
Temporary Table with JOIN
SELECT
e.EmployeeId,
e.EmployeeName,
d.DepartmentName
INTO #EmployeeDetails
FROM Employees e
INNER JOIN Departments d
ON e.DepartmentId = d.DepartmentId;
Temporary tables can store the result of complex queries and joins for further processing.
Temporary Table in Stored Procedure
Temporary tables are frequently used inside stored procedures to store intermediate results.
CREATE PROCEDURE GetITEmployees
AS
BEGIN
CREATE TABLE #ITEmployees
(
EmployeeId INT,
EmployeeName VARCHAR(100),
Salary DECIMAL(10,2)
);
INSERT INTO #ITEmployees
SELECT
EmployeeId,
EmployeeName,
Salary
FROM Employees
WHERE Department = 'IT';
SELECT *
FROM #ITEmployees;
END;
Advantages of Temporary Tables
- Useful for storing intermediate results.
- Useful for complex queries.
- Can be indexed when appropriate.
- Support INSERT, UPDATE and DELETE operations.
- Useful inside stored procedures and complex database operations.
Important Points
- Local temporary tables use a single #.
- Global temporary tables use ##.
- Temporary tables are stored in the SQL Server tempdb database.
- Temporary tables can be queried like normal tables.
- They can be explicitly removed using DROP TABLE.
Summary
Temporary Tables are useful for storing intermediate data during SQL Server operations.
SQL Server supports Local Temporary Tables using # and Global Temporary Tables using ##.