Temporary Tables in SQL Server

Temp tables live in the tempdb database.

#LocalTemp   : visible only to YOUR session (query window).
               Dropped automatically when the session closes
               (or when the stored procedure that created it ends).
##GlobalTemp : visible to ALL sessions. Dropped when the creating
               session closes and nobody else is using it.

Why use them?
  - Store an intermediate result and reuse it in many statements
  - Break a huge query into simple steps
  - You can add indexes and statistics (unlike CTEs)

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Part A: CREATE a Temp Table (two ways)

-- Always drop first, so the script can be re-run
DROP TABLE IF EXISTS #ActiveEmployees;
DROP TABLE IF EXISTS #DeptSummary;
DROP TABLE IF EXISTS #MonthlySales;
DROP TABLE IF EXISTS #Staging;
DROP TABLE IF EXISTS ##SharedLookup;
GO

Way 1: CREATE Table + INSERT

CREATE TABLE #ActiveEmployees
(
    EmployeeID   INT PRIMARY KEY,
    FullName     VARCHAR(101),
    DepartmentID INT,
    Salary       DECIMAL(10,2)
);

INSERT INTO #ActiveEmployees (EmployeeID, FullName, DepartmentID, Salary)
SELECT EmployeeID, FirstName + ' ' + LastName, DepartmentID, Salary
FROM dbo.Employees
WHERE IsActive = 1;

SELECT * FROM #ActiveEmployees;

(24 rows affected)

Way 2: SELECT ... INTO (creates the table automatically from the query)

SELECT d.DepartmentID, d.DepartmentName,
       COUNT(e.EmployeeID) AS EmpCount,
       SUM(e.Salary)       AS TotalSalary
INTO #DeptSummary
FROM dbo.Departments d
LEFT JOIN #ActiveEmployees e ON e.DepartmentID = d.DepartmentID
GROUP BY d.DepartmentID, d.DepartmentName;

SELECT * FROM #DeptSummary ORDER BY TotalSalary DESC;

(8 rows affected)

Where does it live? Look in tempdb (SQL Server adds a suffix to the name)

SELECT name, create_date
FROM tempdb.sys.tables
WHERE name LIKE '#ActiveEmployees%' OR name LIKE '#DeptSummary%';

Part B: Multi-step Report Using Temp Tables

Goal : monthly sales per region + % change from previous month

-- Step 1 : aggregate into a temp table
SELECT Region,
       DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS SaleMonth,
       SUM(TotalAmount) AS Revenue,
       SUM(Quantity)    AS Units
INTO #MonthlySales
FROM dbo.Sales
GROUP BY Region, DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1);

-- Step 2 : add an index (possible on temp tables!)
CREATE CLUSTERED INDEX CIX_MonthlySales ON #MonthlySales (Region, SaleMonth);

-- Step 3 : add a new column and fill it
ALTER TABLE #MonthlySales ADD PrevMonthRevenue DECIMAL(14,2) NULL;

UPDATE cur
SET PrevMonthRevenue = prev.Revenue
FROM #MonthlySales cur
LEFT JOIN #MonthlySales prev
       ON prev.Region = cur.Region
      AND prev.SaleMonth = DATEADD(MONTH, -1, cur.SaleMonth);

-- Step 4 : final result
SELECT Region, SaleMonth, Revenue, PrevMonthRevenue,
       CAST(100.0 * (Revenue - PrevMonthRevenue) / NULLIF(PrevMonthRevenue, 0) AS DECIMAL(8,2)) AS PctChange
FROM #MonthlySales
ORDER BY Region, SaleMonth;

(24 rows affected)
(24 rows affected)

Part C: Staging / Data Cleaning with a temp table (typical ETL step)

CREATE TABLE #Staging
(
    RawName  VARCHAR(100),
    RawCity  VARCHAR(50),
    RawPhone VARCHAR(30),
    RawDate  VARCHAR(20)
);

INSERT INTO #Staging VALUES
('  ravi traders ', 'hyderabad ', '98765-00001',  '2025-01-15'),
('NEW CUSTOMER A',  'Pune',       '(987) 650-0099','15/02/2025'),
('new customer b',  ' Mumbai',    NULL,           '2025-13-01'),   -- bad date
('Sample Co',       'Chennai',    '9876500100',   '2025-03-20');

-- Clean the data
SELECT
    UPPER(LEFT(LTRIM(RTRIM(RawName)), 1)) + LOWER(SUBSTRING(LTRIM(RTRIM(RawName)), 2, 100)) AS CleanName,
    LTRIM(RTRIM(RawCity))                                                                   AS CleanCity,
    REPLACE(REPLACE(REPLACE(REPLACE(RawPhone, '-', ''), '(', ''), ')', ''), ' ', '')        AS CleanPhone,
    COALESCE(TRY_CONVERT(DATE, RawDate, 23), TRY_CONVERT(DATE, RawDate, 103))               AS CleanDate,
    CASE WHEN COALESCE(TRY_CONVERT(DATE, RawDate, 23), TRY_CONVERT(DATE, RawDate, 103)) IS NULL
         THEN 'Invalid date' ELSE 'OK' END                                                  AS ValidationStatus
FROM #Staging;

(4 rows affected)

Part D: Global Temp Table

Open a SECOND query window and run: SELECT * FROM ##SharedLookup; It works while this window is still open.

CREATE TABLE ##SharedLookup (Code CHAR(2) PRIMARY KEY, Description VARCHAR(30));
INSERT INTO ##SharedLookup VALUES ('DL', 'Delivered'), ('SH', 'Shipped'), ('PN', 'Pending'), ('CN', 'Cancelled');
SELECT * FROM ##SharedLookup;

(4 rows affected)

Part E: Temp Table Scope inside a stored procedure

GO
CREATE OR ALTER PROCEDURE dbo.usp_TempTableScopeDemo
AS
BEGIN
    SET NOCOUNT ON;
    CREATE TABLE #InsideProc (ID INT);
    INSERT INTO #InsideProc VALUES (1), (2), (3);
    SELECT 'Inside proc' AS WhereAmI, COUNT(*) AS RowsInTemp FROM #InsideProc;

    -- The procedure CAN see temp tables created by the caller:
    SELECT 'Caller temp table seen from proc' AS Info, COUNT(*) AS Cnt FROM #ActiveEmployees;
END;
GO

EXEC dbo.usp_TempTableScopeDemo;

-- This fails if you uncomment it : #InsideProc was dropped when the proc ended
-- SELECT * FROM #InsideProc;

Part F: Clean Up (good habit)

DROP TABLE IF EXISTS #ActiveEmployees;
DROP TABLE IF EXISTS #DeptSummary;
DROP TABLE IF EXISTS #MonthlySales;
DROP TABLE IF EXISTS #Staging;
DROP TABLE IF EXISTS ##SharedLookup;
GO

TEMP TABLE vs TABLE VARIABLE vs CTE  (summary)
| Feature            | #Temp table     | @Table variable   | CTE           |
|--------------------|-----------------|-------------------|---------------|
| Stored in tempdb   | Yes             | Yes               | No (logical)  |
| Statistics         | Yes             | No (limited)      | n/a           |
| Indexes            | Any, any time   | Only inline (PK, UNIQUE, INDEX) | No |
| Scope              | Session / proc  | Batch only        | One statement |
| Rolled back by TRAN| Yes             | NO                | n/a           |
| Best for           | Large data      | Small data (<1000 rows) | Readability |

PRACTICE QUESTIONS 1. Put all 2025 orders with their totals into #Orders2025 and find the top 5 customers. 2. Create #ProductStock with stock value (StockQty * CostPrice) and list products below 1 lakh. 3. Use SELECT INTO to copy Employees of IT into a temp table, add a Bonus column, set it to 10% of salary.