How to Create a Temporary Table with SQL

intro

Relational database management systems store information in tables. Tables may be similar to a spreadsheet in Excel but with more structure and constraints to ensure the integrity of our data. In this blog, we will look at what a temporary SQL table is, also referred to as a Temp Table, how we can learn to create it, its importance, and its pros and cons.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT

Preface

Why the need for temporary SQL tables in the first place? As opposed to permanent tables, temporary tables offer lots of real-time advantages. This is evident in many cases. An example is in the processing of large batches of data, where temporary tables are created to hold data, perform operations, and then move the processed data to the main tables to allow for the efficient manipulation of the large batches of data.

In the backup of data during bulk data deletions or updates, temporary tables can be used to store a backup of the data to act as a safety net allowing the lost data to be recovered provided something goes wrong.

Other advantages of temporary SQL tables are in testing and debugging and also in ETL (Extract, Transform, and Load) processes where they’re used as staging areas.

What is a temp table in SQL?

A temp table, also known as a temporary SQL table is a type of table that allows our database to store and process intermediate results. It is used in a particular session within the context of a specific session in a database management system. This database management system could be PostgreSQL, MySQL, MongoDB, etc. Due to the nature of temporary tables, they are automatically deleted when not in use, as they do not persist for the long term. Mention that if you create tables yourself, you have to drop tables once they're no longer necessary.

A temporary table is only visible to the session that created it. What does this mean? This means other users or sessions cannot access this table if the table is created by a session in the database that modifies data. Once the session ends, the temporary table gets dropped by default. This is only valid when the temporary table is created by the database itself, however, if it’s created by us, we’ll need to drop it ourselves.

Let’s look at some more details of these tables.

Key Characteristics of SQL Temp Tables

Since temporary SQL tables are different from permanent tables, some key characteristics differentiate the two. Let us look at some of these key characteristics:

  • Naming Convention: In many relational database management systems (RDBMS), temporary tables are typically prefixed with a hash (#). For example, in SQL Server, a temporary table might be named #MyTempTable.
  • Range: A temporary table is usually only visible to the session that created it. This means other users or sessions cannot access this table. Once the session ends, the temporary table gets dropped automatically. You may also create tables to work with data inside of them and then drop them afterward - it's also a temporary table.
  • Storage: Temporary tables are typically stored in a special area of the database, usually referred to as the “tempdb.” Note that this is concerning Microsoft SQL server only and works differently in other DBMSs.
  • Overhead: Creating and dropping temporary tables incurs some overhead. If done excessively, it can lead to performance issues. You need to ensure you have sufficient space on the disk for your DBMS to deal with temp tables and make sure you know that temp tables occupy space too.

Now that we’ve learned some features of temporary SQL tables, let’s learn the differences between temporary and permanent SQL tables.

FeaturePermanent TableTemporary Table
TimeRemains in the database until manually dropped.Usually deleted after the session ends or after the procedure that created it finishes.
AccessibilityAccessible to all sessions and users.Accessible to only sessions or users that created it except global temporary tables.
Naming ConventionPermanent table names are not prefixed with any special characterOften prefixed with a special character or keyword
Triggers and DependenciesCan have associated triggers, views, and other dependencies.Typically do not have such long-term dependencies
Indexes and ConstraintsCan have primary keys, foreign keys, indexes, and other constraints.Can have indexes and constraints, but they are temporary and are dropped when the changes are made to the original table

Now, let us learn how to create a temporary table with SQL.

How to Create a SQL temp table

Temporary tables are created once your database decides it's time to use them after you run a query. However, it can be created by a user manually.

To create a temporary SQL table manually, we can use the CREATE TABLE statement with the TEMPORARY or TEMP keyword before the table name but it is important to note the syntax variation amongst different relational database management system softwares.

Here's how you can create a temporary SQL table in a few popular RDBMSs:

In SQL Server:

SQL Server Syntax in the SQL Commander of DbVisualizer
SQL Server Syntax in the SQL Commander of DbVisualizer
Copy
        
1 CREATE TABLE #TempTable ( 2 ID INT PRIMARY KEY, 3 NAME NVARCHAR(100) 4 );

In SQL Server, local temporary tables are prefixed with a single hash (#). If you use a double hash (##), it creates a global temporary table accessible by all sessions, but it will still be temporary.

In PostgreSQL:

PostgreSQL Syntax for Temporary Tables in DbVisualizer
PostgreSQL Syntax for Temporary Tables in DbVisualizer
Copy
        
1 CREATE TEMP TABLE TempTable ( 2 ID SERIAL PRIMARY KEY, 3 Name TEXT 4 );

In PostgreSQL, you use the TEMP or TEMPORARY keyword to specify that a table should be temporary. In PostgreSQL, temporary tables are always session-specific, however, they can (and are very likely to) be created automatically if your data is big enough.

Code Analysis:

When we look at the two syntaxes for the function above, we see that

  1. The CREATE TEMP TABLE statement is used to create a temporary table.
  2. TempTable is the name given to the temporary table and can be customized to suit our needs.
  3. We define the columns of the temporary table inside the parentheses.
  4. We can add more columns, specifying the column name followed by the data type.

SQL temp tables - Use Cases

Temporary SQL tables can be extremely useful in some cases to manage intermediate results, simplify complex operations, or improve performance. Below are some use cases:

Staging Data for Import

Imagine we’re importing data from a CSV file into our main database table. We might want to import data into a temporary table for validation and transformation before the final insertion.

This is how we’re going to do it 👇

Copy
        
1 -- Create a temporary table 2 3 CREATE TABLE #ImportedData ( 4 Name VARCHAR(100), 5 Age INT, 6 Email VARCHAR(100) 7 ); 8 9 -- Assume we've imported data from a CSV to #ImportedData here 10 11 -- Validate and insert into main table 12 INSERT INTO MainTable (Name, Age, Email) 13 SELECT Name, Age, Email 14 FROM #ImportedData 15 WHERE Age > 18; 16 17 -- Drop the temporary table 18 DROP TABLE #ImportedData

In the code above, the #ImportedData table acts as a staging area, which allows for validation or transformation operations before committing the data to its primary database. In this specific case, the code is designed to filter and only transfer records of individuals older than 18 years from the temporary table to a main table named MainTable.

Once the validated data is inserted into the main table, the temporary staging table, #ImportedData, is dropped to free up resources. The staging area in data operations ensures that the primary table (MainTable) only receives data that meets specific criteria, maintaining data integrity and potentially optimizing performance.

Data Transformation and Aggregation

Another use case of temporary SQL tables is in data transformation and aggregation. Suppose we have a sales table and we want to aggregate sales by month but also want to perform further transformations on this aggregated data.

Let us build the query:

Copy
        
1 - - Create a temporary table 2 3 CREATE TABLE #MonthlySales ( 4 SalesMonth DATE, 5 TotalSales DECIMAL(10,2) 6 ); 7 8 - - Insert aggregated sales data into the temporary table 9 INSERT INTO #MonthlySales (SalesMonth, TotalSales) 10 SELECT DATEFROMPARTS(YEAR(SaleDate), MONTH(SaleDate), 1) AS SalesMonth, 11 SUM(SaleAmount) AS TotalSales 12 FROM Sales 13 GROUP BY YEAR(SaleDate), MONTH(SaleDate); 14 15 - - Query from the temporary table for further analysis 16 SELECT SalesMonth, TotalSales 17 FROM #MonthlySales 18 WHERE TotalSales > 10000; 19 20 - - Drop the temporary table 21 DROP TABLE #MonthlySales

Improving Query Performance

Temporary tables can be used to streamline complicated or resource-intensive searches. The quantity of data handled at each stage can be decreased or interim results can be pre-computed when a complex query is divided into several phases utilizing temporary tables.

Here's an illustration:

Copy
        
1 - - Create a temporary table to store intermediate results 2 CREATE TEMPORARY TABLE temp_results AS 3 SELECT column1, COUNT(*) AS count_value 4 FROM large_table 5 WHERE condition1 6 GROUP BY column1; 7 8 - - Use the temporary table to optimize the final query 9 SELECT column1, column2 10 FROM temp_results 11 WHERE count_value > 10 12 ORDER BY column1; 13 14 - - Drop the temporary table 15 DROP TABLE temp_results

In the first part of the code above, a temporary table named temp_results is created to hold specific aggregated data from the large_table table. This data is grouped by column1 and has a count of rows for each group.

In the second part, instead of going back to the original large_table, we query the smaller and more concise temp_results table. This can be a more efficient operation, especially if large_table is very large or the computations on it are very costly. By using the temporary table, we filter and order the results based on the aggregated count and other criteria.

Lastly, the temporary table is explicitly dropped. Although the table would be automatically removed at the end of the session, dropping it manually is a good practice to ensure immediate resource release.

Things to Note

Keep the following things in mind when working with temporary tables:

  • CRUD operations can be performed on temporary SQL tables, just like permanent SQL tables.
  • Always keep in mind that temporary tables are created automatically when the DBMS deems it necessary, but can also be created manually.
  • Be mindful of resource usage. There isn’t so much you can do if your database decides to use temporary tables. If you create a temporary table yourself, you can change things, but if you run ALTER queries they're built and destroyed automatically. The take-home message is that you need to be mindful of resource usage and always keep in mind how much free space you have on the disk or memory when dealing with temporary tables.

Conclusion

Temporary SQL tables are a powerful tool for working with relational databases, allowing you to carefully manage your database and optimize queries. Whether you're working with simple queries or complex ones, having a solid understanding of temporary SQL tables is essential for any data analyst or database developer.

By following this blog, you can quickly reference the syntax for creating temporary SQL tables in some particular RDBMSs. With this knowledge, you'll be equipped to tackle even the most challenging scenarios in the database space.

Although temporary SQL tables are an important aspect of database development, they're just one piece of the puzzle. To truly optimize your database performance, availability, capacity, and security, it's important to employ a range of tools and techniques. Solutions like DbVisualizer offer a powerful SQL client with advanced features and support for a wide range of database management systems. With the right tools and expertise, you can take your database development to the next level. Grab the free evaluation trial and get hands-on experience with the features!

FAQs

What is a temporary SQL table, and why would I use one?

A temporary SQL table is a type of table that allows you to store and process intermediate results. It is used in a particular session within the context of a specific session in a database management system. Once the need to use a temporary table arises, you can create one yourself, store data in it, and get rid of it once it is no longer required.

How do I create a temporary table in SQL?

If a DBMS deems temporary tables necessary, it creates it automatically. However, the method to create a temporary table can vary based on the database system you're using. In Microsoft SQL Server, for instance, you can create a temporary table using the following syntax:

SQL Query in DbVisualizer
SQL Query in DbVisualizer
Copy
        
1 CREATE TABLE #TemporaryTableName ( 2 Column1 DataType1, 3 Column2 DataType2, 4 ... 5 );
About the author
Leslie S. Gyamfi.
Leslie S. Gyamfi
Leslie Gyamfi is a mobile/web app developer with a passion for creating innovative solutions. He is dedicated to delivering high-quality products and technical articles. You can connect with him on LinkedIn
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL Server for Mac: The Ultimate Guide to the Best SQL Server Client

author TheTable tags SQL SERVER 7 min 2024-03-01
title

5 Ways to Split a String in PostgreSQL

author Antonello Zanini tags POSTGRESQL Split 6 min 2024-02-29
title

MySQL IFNULL – Everything You Need to Know

author Leslie S. Gyamfi tags IFNULL MySQL 6 min 2024-02-26
title

PostgreSQL CASE: A Comprehensive Guide

author Leslie S. Gyamfi tags CASE POSTGRESQL 7 min 2024-02-22
title

Outer Join in SQL: A Comprehensive Guide

author Ochuko Onojakpor tags JOIN 11 min 2024-02-19
title

SQL DDL: The Definitive Guide on Data Definition Language

author Antonello Zanini tags DDL SQL 7 min 2024-02-15
title

SQL Views: A Comprehensive Guide

author Bonnie tags VIEWS 5 min 2024-02-12
title

MySQL CREATE DATABASE Statement: Definitive Guide

author Antonello Zanini tags Create database MySQL 7 min 2024-02-08
title

Discover All PostgreSQL Data Types

author Antonello Zanini tags DATA TYPES POSTGRESQL 12 min 2024-02-05
title

Dangerous Big Data - Big Data Pitfalls to Avoid

author Lukas Vileikis tags BIG DATA min 2024-02-01

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗