SQL Temp Table: How to Create One [Step-by-Step Guide]

Incredibly beneficial in handling structured data, structured query language (SQL) is designed to interact with relational databases, enabling efficient data creation, manipulation, and management. While other use cases exist, SQL is mainly used in business applications, such as CRM and ERP, for data storage and retrieval. It’s also widely used in analytics to extract data from databases and create reports.

SQL excels in managing structured data organized in tables with rows and columns. It’s efficient for querying large datasets, performing aggregations, and joining data from multiple tables. However, it lacks when it comes to handling unstructured data (text, images, videos, etc.) and manipulating massive datasets with high velocity.

As the bedrock of relational databases, tables are fundamental in SQL. Deviating from our standard article, we’ll discuss a unique table called the SQL temp table, which exists only to be dropped.

An SQL temporary table, or simply a temp table, is a provisional feature in SQL that allows you to create, manipulate, and delete results during the duration of a database session, transaction, or a specific batch of SQL statements.

For example, temporary tables created with a single # are automatically dropped when the session or batch ends. The query could look like:

BEGIN
    -- Create and use a local temporary table
    CREATE TABLE #EmployeeTemp (
        EmployeeID int,
        FirstName varchar(50),
        LastName varchar(50),
        Salary decimal(10, 2)
    );

    INSERT INTO #EmployeeTemp (EmployeeID, FirstName, LastName, Salary)
    VALUES (1, 'John', 'Doe', 60000.00),
           (2, 'Jane', 'Smith', 75000.00);

    SELECT * FROM #EmployeeTemp;
END;

-- The temporary table #EmployeeTemp is automatically dropped after the batch ends

Temp tables have distinct characteristics that set them apart from the regular tables used for permanent data storage.

In contrast to regular SQL tables, temp tables can have local and global existence, depending on their visibility. As expected, local temp tables are private to the session or connection that created them. In contrast, the global temporary tables can be visible to all connections within the same database session.

Despite the convenience and performance boost, temp tables can’t be used beyond the basic CRUD operations and joins for data manipulation. More elaborate functionalities, like triggers or complex constraints, may not be supported.

Let’s walk through how to make temporary tables in SQL and handle them efficiently:

Creating a General Temporary Table in SQL

Temporary tables are created using the CREATE TABLE statement with keywords like TEMPORARY or TEMP before the chosen table name.

CREATE TEMPORARY TABLE order_details (
  order_id INT,
  product_id INT,
  quantity INT,
  unit_price DECIMAL(10,2)
);

Creating a Local Temporary Table in SQL

Local temporary tables are prefixed with a single # and are available only within the session that created them. They are automatically dropped when the session ends.

Example:

CREATE TABLE #EmployeeTemp (
    EmployeeID int,
    FirstName varchar(50),
    LastName varchar(50),
    Salary decimal(10, 2)
);

Creating a Global Temporary Table

Global temporary tables are prefixed with the ## symbol and are available to all sessions. They are dropped when the session that created them ends and all other sessions that are referencing the table have also ended.

Example:

CREATE TABLE ##ProjectTemp (
    ProjectID int,
    ProjectName varchar(100),
    Budget decimal(18, 2)
);

Inserting Data into Temporary Tables

You can insert data into temporary tables using the INSERT INTO statement, just as you would with regular tables.

INSERT INTO #EmployeeTemp (EmployeeID, FirstName, LastName, Salary)
VALUES 
    (1, 'John', 'Doe', 60000.00),
    (2, 'Jane', 'Smith', 75000.00);

-- Insert data into the global temporary table
INSERT INTO ##ProjectTemp (ProjectID, ProjectName, Budget)
VALUES 
    (101, 'Project Alpha', 500000.00),
    (102, 'Project Beta', 750000.00);

Querying Data from Temporary Tables

You can query data from temporary tables using standard SQL SELECT statements.

-- Select data from the local temporary table
SELECT * FROM #EmployeeTemp;

-- Select data from the global temporary table
SELECT * FROM ##ProjectTemp;

Dropping Temporary Tables

Temp tables should be dropped once you’re done with them to free up resources. However, they’re automatically dropped once the session ends. Here is how to do it manually:

-- Drop the local temporary table
DROP TABLE #EmployeeTemp;

-- Drop the global temporary table
DROP TABLE ##ProjectTemp;

The complete example for the local temp table would look like:

-- Start of local temporary table example

-- Create a local temporary table
CREATE TABLE #EmployeeTemp (
    EmployeeID int,
    FirstName varchar(50),
    LastName varchar(50),
    Salary decimal(10, 2)
);

-- Insert data into the local temporary table
INSERT INTO #EmployeeTemp (EmployeeID, FirstName, LastName, Salary)
VALUES 
    (1, 'John', 'Doe', 60000.00),
    (2, 'Jane', 'Smith', 75000.00);

-- Select data from the local temporary table
SELECT * FROM #EmployeeTemp;

-- Drop the local temporary table
DROP TABLE #EmployeeTemp;

-- End of local temporary table example

Some advantages of using SQL temp tables are:

Intermediate Result Storage

Multi-layered nested subqueries often jeopardize query performance and simplicity. Temp tables in SQL resolve the issue by storing intermediate results in complex operations. This aids in debugging, understanding the query flow, and achieving better performance.

Furthermore, having a platform to store intermediate results simplifies the overall logic and readability.

Example:

-- Step 1: Store intermediate results in a temporary table
CREATE TABLE #IntermediateResults AS
SELECT 
    OrderID, 
    SUM(Quantity) AS TotalQuantity
FROM 
    OrderDetails
GROUP BY 
    OrderID;

-- Step 2: Use the intermediate results in further calculations
SELECT 
    CustomerID, 
    ir.TotalQuantity
FROM 
    Orders o
JOIN 
    #IntermediateResults ir ON o.OrderID = ir.OrderID;

Breaking Down Complex Queries

As mentioned, you can improve readability and maintainability by breaking down complex queries into smaller, manageable parts using temporary tables. This approach also makes it easier to test individual parts of the query.

Subset Analysis

Subset analysis involves focusing on a specific portion of a larger dataset to gain insight without affecting the entire database. This is particularly useful when the dataset is comparatively larger and requires the prevention of accidental modification. Temporary tables allow you to analyze specific subsets of data without modifying the original tables.

Example:

-- Create a temporary table to store a subset of customer data
CREATE TABLE #TopCustomers AS
SELECT 
    CustomerID, 
    CustomerName, 
    TotalSpent
FROM 
    Customers
WHERE 
    TotalSpent > 10000;

-- Perform analysis on the subset
SELECT 
    AVG(TotalSpent) AS AverageSpent
FROM 
    #TopCustomers;

Performance Improvement

By storing intermediate results, temp tables allow you to avoid redundant calculations and reduce the number of joins, improving query performance and reducing the load on the database.

Easier Data Transformation

Temporary tables act as a flexible workspace for transforming data into a desired format before analysis or reporting. You can manipulate the data within the temp table without affecting the source tables. This simplifies the transformation process and avoids altering the original data.

For example, you might want to create a temp table with a single “full_name” column for reporting purposes from a customer table having separate columns for “first_name” and “last_name.”

Reducing Lock Contention

Database management systems lock particular resources when being used to ensure data integrity. Lock contention occurs when multiple processes simultaneously attempt to access the same database resources, such as rows, pages, or tables. This can lead to performance degradation and increase the risk of deadlocks, where two or more transactions are stuck waiting for each other to release locks.

Temp tables may help the issue by isolating heavy read/write operations, reducing the need to lock main database resources.

Example:

-- Use a temporary table to perform heavy read/write operations
CREATE TABLE #OrderUpdates AS
SELECT 
    OrderID, 
    OrderStatus
FROM 
    Orders
WHERE 
    OrderStatus = 'Pending';

-- Update the temporary table
UPDATE #OrderUpdates
SET OrderStatus = 'Processed';

-- Apply the updates back to the main table
UPDATE o
SET o.OrderStatus = ou.OrderStatus
FROM Orders o
JOIN #OrderUpdates ou ON o.OrderID = ou.OrderID;

Hopefully, you’ll now be able to leverage the in-built temp table feature to create SQL temp tables and gain an edge on query performance, complex subqueries, and lock contentions. If you want to learn more about SQL functions and interview questions, follow our SQL Learning Path and Company Interview Guides. All the best!