How to Create Temporary Table in SQL Server Management Studio?

by

In this SQL Server tutorial, I will show you how to create temporary table in SQL Server Management Studio.

First, you will understand ‘What is the temporary table?’, then you learn why a temporary table is created.

After that, I will explain the types of temporary tables that you can create in SQL Server. Finally, you will learn for example how to create local and global temporary tables.

Also, you will know where the temporary table is stored on the SQL Server instance.

Create Temporary Table in SQL Server Management Studio

Before proceeding to create temporary table in SQL Server Management Studio, you need to know ‘What is the temporary table?’.

The temporary tables are like other tables in a database but they exist for a shorter time. A temporary or temp table is created based on the requirement and dropped or deleted when you are done using them.

Consider a situation where you have data in big size and want to perform some calculations on that data. So instead of performing calculations on big original data, you create a temporary table containing that data to perform calculations.

So in SQL Server, you can create temporary tables that exist until the database session. As soon as you end the database session, the temporary tables are deleted or dropped automatically.

But why create temp tables,

  • You create temp tables for storing intermediate results when performing complex queries.
  • Keep data in isolation to avoid interference with other users or sessions, for that you will create temporary tables.
  • If you need to access the same data or compute something again and again. Then you can create a temporary table for that to store the data or precomputed value to enhance the query performance.
  • If you have complex tasks, then you can break them down the complex task into manageable tasks by creating a temporary table for each smaller task. Then work on each temp table separately.

So you can manage the data by creating temporary tables within a session without affecting the original database tables.

Now I hope you have a clear understanding of temporary tables in SQL Server, let’s move and learn about types of temporary tables.

In SQL Server, there are two types of temporary tables:

  • Local temporary tables: When you create this kind of temporary tables, you can access them within the current session and when the session ends, this temp table is deleted automatically. Only the user who creates the temp table can access it.
  • Global temporary tables: The global tables are created and accessed across different sessions. In other words, you create a global table and this table can be accessed by multiple users who have a separate session in the database.
    • Same as the local temporary tables, the global temporary tables are also deleted whenever the database session ends.

Let’s create both kinds of tables using the SQL Server Management Studio.

So open the SSMS and connect to the SQL Server instance, if you haven’t installed it, then refer to the tutorial Install SQL Server Management Studio.

Create Temporary Table in SQL Server Management Studio: Local Table

To create a local temporary table in SQL, follow the below syntax.

CREATE TABLE #tmpe_tablename(
column1 datatype1,
column2 datatype2,
...
);

Where,

  • CREAT TABLE: It is the command to create a new table.
  • #temp_tablename: Name of the temporary table that you want to create. When specifying the name of a table always prefix the name with the single pound sign(#) to make that table a local temporary table.
  • column1 datatype1: Define the columns and their data type of the temporary table.

Now use the above syntax to create a real table in SQL Server. So open the query editor by pressing the CTRL+N from your keyboard.

After that write the below query to create a table named ‘Customer’ which contains the customer information.

CREATE TABLE #Customer(
customer_id INT,
name VARCHAR(40),
country VARCHAR(60)
);
Create Temporary Table in SQL Server Management Studio Local Table

When you execute the above query by clicking on the Execute button in the SSMS, the new local temporary Customer table is created with three columns customer_id of type INT, name of type VARCHAR(40), and country of type VARCHAR(60).

Remember temporary tables in SQL Server are stored within the Temporary Tables of tempdb database of System Databases as shown in the below picture.

View Local Temporary Table in SQL Server Management Studio

Look at the above picture, the dbo.#Customer is your local temporary table that you have created.

But as you know, temporary tables exist for a shorter time, or temporary table is deleted automatically whenever the database session ends.

So end the database session by closing and starting SSMS, and then go to the same folder Temporary Tables to see the existence of the local temporary Customer table.

As soon as you end the database session, you won’t see the local temporary tables.

Create Temporary Table in SQL Server Management Studio: Global Table

To create a global temporary table in SQL, follow the below syntax.

CREATE TABLE ##tmpe_tablename(
column1 datatype1,
column2 datatype2,
...
);

Where,

  • CREAT TABLE: It is the command to create a new table.
  • ##temp_tablename: Name of the global temporary table that you want to create. When specifying the name of a table always prefix the name with the double pound sign(##) to make that table a global temporary table.
  • column1 datatype1: Define the columns and their data type of the temporary table.

Now write the below query to create a global temporary Product table.

CREATE TABLE ##Product(
product_id INT,
product_name VARCHAR(40),
product_description VARCHAR(60)
);
Create Temporary Table in SQL Server Management Studio Global Table

When you execute the above query by clicking on the Execute button in the SSMS, the new global temporary Product table is created with three columns product_id of type INT, product_name of type VARCHAR(40), and product_description of type VARCHAR(60).

Now to check the global temporary table, go to the Temporary Tables folder of the tempdb database as shown in the below picture.

View Global Temporary Table in SQL Server Management Studio

When you visit the Temporary Tables folder, you see the global temporary dbo.##Product table as shown in the above picture.

If you go to the same Temporary Tables with different sessions, you will see the same global temporary table.

Now again close the SSMS and come back to check the existence of the global temporary table. If you don’t see the global temporary table, it means it is deleted as the current database session ends.

This is how to create temporary table in SQL Server Management Studio using the single pound(#) and double pound(##) with the CREATE TABLE statement to create a local and global temporary table.

Conclusion

In this SQL Server tutorial, you learned how to create a temporary table in SQL Server. You learned about two types of temporary tables, local and global temporary tables. Also, you learned why to create a temporary table.

You may like to read the following tutorials: