SQL Temporary Table: Syntax, Usage, and Examples
A SQL temporary table is a table that exists only for the duration of a session or transaction. You can use it to store intermediate results, manipulate data without affecting permanent tables, and optimize query performance when working with large datasets.
How to Use SQL Temporary Tables
You create a temporary table in SQL using the CREATE TEMPORARY TABLE statement. This table remains available during your session but disappears once the session ends. You can create temporary tables explicitly or derive them from an existing dataset.
Basic Syntax for Creating a Temporary Table
sql
CREATE TEMPORARY TABLE temp_table_name (
column1 datatype,
column2 datatype
);
This creates a temporary table with specific columns and data types that you define. The table exists only for your current session.
You can also create and populate a temporary table using SELECT INTO:
sql
SELECT column1, column2
INTO #temp_table
FROM existing_table
WHERE condition;
This copies selected columns from an existing table into a temporary table, allowing you to work with a subset of data without modifying the original table.
If you need to check whether a temporary table exists before creating or deleting it, use this approach:
sql
IF OBJECT_ID('tempdb..#temp_table') IS NOT NULL
PRINT 'Temporary table exists';
This prevents errors when running scripts that depend on the table.
When to Use SQL Temporary Tables
Storing Intermediate Query Results
When working with complex queries, breaking them into smaller parts with temporary tables improves readability and efficiency. Instead of repeating subqueries, store results in a temporary table and reference it later.
sql
CREATE TEMPORARY TABLE temp_results AS
SELECT customer_id, COUNT(order_id) AS order_count
FROM orders
GROUP BY customer_id;
This creates a temporary table to store the number of orders per customer. By using this table in later queries, you avoid recalculating the same counts repeatedly.
Manipulating Large Datasets
You can filter and modify large datasets in a temporary table before inserting them into permanent tables.
sql
SELECT * INTO #filtered_data
FROM large_table
WHERE condition;
This extracts specific data from a large table and stores it in a temporary table, allowing you to process only the relevant records.
Optimizing Query Performance
Re-running a query multiple times on a large dataset slows performance. Storing intermediate calculations in a temporary table saves time.
sql
SELECT AVG(price) AS avg_price
INTO #temp_avg_price
FROM products;
This stores the average product price in a temporary table so that you can reference it without recalculating.
Examples of SQL Temporary Tables
Example 1: Creating and Inserting Data
sql
CREATE TEMPORARY TABLE #temp_users (
user_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO #temp_users (user_id, name, email)
VALUES (1, 'Alice', 'alice@example.com'),
(2, 'Bob', 'bob@example.com');
SELECT * FROM #temp_users;
This creates a temporary table to store user details, inserts two users, and retrieves all records using SELECT *.
Example 2: Using SELECT INTO to Create a Temporary Table
sql
SELECT order_id, customer_id, total_amount
INTO #temp_orders
FROM orders
WHERE total_amount > 500;
This selects orders with a total amount greater than 500 and stores them in a temporary table, allowing you to analyze high-value transactions separately.
Example 3: Updating a Temporary Table
sql
UPDATE #temp_users
SET name = 'Charlie'
WHERE user_id = 1;
This updates the name column for the user with user_id 1. Temporary tables support updates just like permanent tables.
Example 4: Joining a Temporary Table with a Permanent Table
sql
SELECT c.customer_name, t.total_amount
FROM customers c
JOIN #temp_orders t ON c.customer_id = t.customer_id;
This joins the #temp_orders table with the customers table to retrieve customer names along with the total amount of their high-value orders. This is useful when working with filtered datasets.
Learn More About SQL Temporary Tables
Deleting a Temporary Table
SQL automatically deletes temporary tables when your session ends, but you can remove them manually:
sql
DROP TABLE #temp_users;
This frees up resources when the table is no longer needed.
Using Global Temporary Tables
A global temporary table, prefixed with ##, remains available across multiple sessions:
sql
CREATE TABLE ##global_temp (
id INT,
description VARCHAR(255)
);
Unlike session-based temporary tables, global temporary tables persist until all active sessions referencing them are closed.
Checking If a Temporary Table Exists
Before creating or deleting a temporary table, check if it exists:
sql
IF OBJECT_ID('tempdb..#temp_orders') IS NOT NULL
DROP TABLE #temp_orders;
This prevents errors by ensuring that the table exists before running a DROP TABLE statement.
SQL temporary tables help you manage temporary datasets, break down complex queries, and optimize performance without affecting permanent tables. You can use them to store filtered data, process calculations, and speed up queries when working with large datasets.