Articles

How to Create a Temp Table in SQL: Step-by-Step Guide with Examples

Learn how to efficiently create a temp table in SQL with this definitive guide. Master SQL temp table creation with practical query examples!

In the world of databases, efficiency and performance are key. As SQL developers or data analysts, we often deal with complex queries, large datasets, and the need to store intermediate results temporarily. This is where temporary tables—commonly known as temp tables in SQL—come into play.

In this comprehensive guide, we’ll have a detailed discussion on temp tables, covering what they are, how they work, and some practical examples for better understanding. We’ll also compare temp tables to permanent tables and highlight some best practices that we can apply to get the most out of this feature.

Let’s start by discussing what a temp table is in SQL and its key characteristics.

What is a temp table in SQL?

A temp table in SQL is a table that exists only for the duration of a session or a specific database operation. It behaves just like a regular table, allowing us to store data, define columns with data types, use constraints, and even create indexes. However, unlike permanent tables, a temp table is automatically deleted when it is no longer needed—typically at the end of the session or when it is explicitly dropped.

The primary purpose of a temp table in SQL is to hold intermediate results temporarily while a query or procedure is being executed. Instead of performing complex operations directly on large or production tables, we can offload the workload to a temp table. This makes our queries cleaner, easier to manage, and often faster to execute.

Key characteristics:

  • Temp tables can only be accessed within the session or connection that created them. Once the session ends, the table is automatically removed.
  • They are physically stored in the tempdb system database, ensuring they don’t interfere with permanent user databases.
  • Local temp tables begin with a single hash (#TableName) and global temp tables with double hashes (##TableName), making them easy to identify.
  • Just like permanent tables, temp tables can have indexes, primary keys, foreign keys, and constraints to enforce data integrity.
  • Temp tables can participate in joins, subqueries, and any other SQL operations that involve regular tables.

In the next section, we’ll learn about the different types of temp tables in SQL.

Related Course

Learn SQL

In this SQL course, you'll learn how to manage large datasets and analyze real data using the standard data management language.Try it for free

Types of temp tables in SQL

When working with temp tables in SQL, it’s important to understand the different types available and when to use each. SQL primarily supports two types of temp tables—local temp tables and global temp tables. Let’s learn about them one by one.

Local temp tables

Local temp tables are written with a single hash symbol (#) at the beginning and are only visible to the session (or connection) that created them.

Scope and lifetime:

These tables exist only for the duration of the session in which they were created. Once the session ends or the table is explicitly dropped, SQL automatically deletes the table.

Use case:

Ideal for storing intermediate data during stored procedures, scripts, or ad hoc queries where data doesn’t need to persist beyond the session.

Example:

This example creates a local temp table named SalesReport:

CREATE TABLE #SalesReport ( 
    Region NVARCHAR(50), 
    TotalSales DECIMAL(10,2) 
);

Global temp tables

Global temp tables are prefixed with two hash symbols (##) and are accessible to all sessions and users on the server.

Scope and lifetime:

These tables remain in the tempdb database until the session that created them ends and all other sessions referencing them are closed. They are shared across connections, making them suitable for multi-session data sharing.

Use case:

Useful in scenarios where multiple users or sessions need access to the same temporary data, such as a centralized staging table for a shared reporting process.

Example:

This example creates a global temp table named UserSessions:

CREATE TABLE ##UserSessions ( 
    UserID INT, 
    LoginTime DATETIME 
);

Now that we know the different types of temp tables in SQL, let’s have a look at the syntax for creating them.

Syntax for creating temp tables

Here is the syntax for creating a local temp table in SQL:

CREATE TABLE #TempTableName ( 
    Column1 DataType [NULL | NOT NULL], 
    Column2 DataType [NULL | NOT NULL], 
    ... 
);

In the syntax, the hash symbol (#) denotes a local temp table, which is accessible only in the current session.

On the other hand, we can use this syntax for creating a global temp table:

CREATE TABLE ##TempTableName ( 
    Column1 DataType [NULL | NOT NULL], 
    Column2 DataType [NULL | NOT NULL], 
    ... 
);

In the syntax, the double hash symbol (##) denotes a global temp table, which can be accessed from any session.

Moreover, in both cases:

  • Column1, Column2, ...: Names of the columns to be included in the table.
  • DataType: Specifies the data type of the column (e.g., INT, VARCHAR, DATETIME).
  • NULL | NOT NULL (Optional): Defines whether the column allows NULL values.

With the syntax covered, let’s learn how to create a temp table in SQL.

How to create a temp table in SQL

To understand how to create a temp table in SQL, let’s walk through a real-world example.

In this example, we’re creating a local temp table named EmployeeTemp:

CREATE TABLE #EmployeeTemp ( 
    EmployeeID INT PRIMARY KEY, 
    FirstName NVARCHAR(50), 
    LastName NVARCHAR(50), 
    Department NVARCHAR(50) 
);

The next step is to insert data into the table:

INSERT INTO #EmployeeTemp (EmployeeID, FirstName, LastName, Department) 
VALUES  
    (1, 'Alice', 'Johnson', 'HR'), 
    (2, 'Bob', 'Smith', 'Finance'), 
    (3, 'Charlie', 'Lee', 'IT');

After inserting the data, run this query:

SELECT *
FROM #EmployeeTemp;

We’ll get an output like this:

EmployeeID FirstName LastName Department
1 Alice Johnson HR
2 Bob Smith Finance
3 Charlie Lee IT

We now know how to create a temp table in SQL as well as how to insert data into it. This means it’s time for us to learn how to manipulate the data stored in a temp table.

How to manipulate a temp table in SQL

In this section, we’ll perform these manipulation operations on the EmployeeTemp temp table:

  • Updating data
  • Deleting data
  • Dropping the table

Let’s go through them one by one.

Updating data in a temp table

This example first identifies the employee with an EmployeeID of 3 in the EmployeeTemp table. Then, it changes the employee’s department from IT to Marketing:

UPDATE #EmployeeTemp
SET Department = 'Marketing'
WHERE EmployeeID = 3;

Then, run this query again:

SELECT *
FROM #EmployeeTemp;

Here is the output:

EmployeeID FirstName LastName Department
1 Alice Johnson HR
2 Bob Smith Finance
3 Charlie Lee Marketing

Deleting data from a temp table

This example removes the employee with an EmployeeID of 2 from the EmployeeTemp table:

DELETE FROM #EmployeeTemp
WHERE EmployeeID = 2;

After that, run this query:

SELECT *
FROM #EmployeeTemp;

Here is the output:

EmployeeID FirstName LastName Department
1 Alice Johnson HR
3 Charlie Lee Marketing

Dropping a temp table

Since we’re done operating on the EmployeeTemp table, we can drop it to free up resources.

This example drops the EmployeeTemp table from the tempdb system database:

DROP TABLE #EmployeeTemp;

We learned quite a bit about temp tables in SQL, but how does it differ from permanent tables? Let’s see that next.

Temp tables vs. permanent tables

Here are the differences between temp tables and permanent tables in SQL:

Feature Temp Tables Permanent Tables
Lifespan Session-based Permanent
Storage Stored in tempdb Stored in user DB
Naming Convention Begins with # or ## Regular table name
Visibility Session/global-based Visible to all users
Use Case Temporary operations Long-term data storage

Last but not least, let’s check out some best practices for creating temp tables in SQL.

Best practices for creating temp tables

Applying these best practices will enable us to create temp tables in SQL efficiently:

  • Use temp tables sparingly: Use temp tables only when needed to avoid unnecessary overhead in tempdb.
  • Index when needed: For large datasets, consider indexing temp tables to optimize performance.
  • Drop explicitly: Although SQL drops them automatically, explicitly dropping helps manage resources better.
  • Avoid name conflicts: Especially with global temp tables, use unique names to avoid conflicts between sessions.

Conclusion

Understanding how to create a temp table in SQL allows us to handle complex queries and transformations efficiently without cluttering the main database. Whether we need them for debugging, staging data, or intermediate results, SQL temp tables offer flexibility and control in our development process.

If you want to expand your knowledge of SQL, check out the Learn SQL course on Codecademy.

Frequently asked questions

1. What is the difference between a local and global temp table?

A local temp table (#TableName) is only visible to the session that created it, while a global temp table (##TableName) is accessible to all sessions.

2. How long does a temp table exist?

Local temp tables exist until the session ends, whereas global temp tables persist until the last session referencing them ends.

3. Do temp tables affect performance?

Yes, temp tables can affect performance, especially if overused or if large volumes of data are stored without indexes. It’s best to monitor their impact using execution plans and optimize where necessary.

Codecademy Team

'The Codecademy Team, composed of experienced educators and tech experts, is dedicated to making tech skills accessible to all. We empower learners worldwide with expert-reviewed content that develops and enhances the technical skills needed to advance and succeed in their careers.'

Meet the full team

Related articles

Learn more on Codecademy

Contents