What is temporary table in Database and SQL? Example Tutorial

Hello folks, if you have heard the term temporary table online or by your colleague in a call and wondering what is temporary table, what is the difference between a normal table and a temporary table, and when and how to use it then you have come to the right place. In this blog, we shall learn how to use a temporary table in SQL. But before we go into that we need an overview of SQL. What is SQL? SQL stands for Structured query language. it is a programming language used in communicating or relating to the relational database. And this programming language performs various forms of operation in the data. It was created in the 1970s, SQL is used by database administrators, and developers writing data integration scripts, etc.

But, What is a temporary table and Why do we need it? 

 As its name indicates, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete), join, and some other operations like the persistent database tables(Non-temporary tables). 

 A table is the basic part of a database whether temporary or persistent and it consists of rows and columns of data. Because, before you can start communicating or manipulating data, you need a table. A table houses the data and as basic as it is, it is very important. A single table holds records, and each record is stored in a row on the table. 

A temporary table is a short-term table, this means that It is a kind of table that automatically deletes once the connection is closed. As the name implies, temporary tables are used to store data temporarily and they can perform CRUD (Create, Read, Update, and Delete) as well. It only stores and processes intermediate results.

Temporary tables are like permanent tables we used to and they are created in the temporary database and it deletes automatically as soon as the last connection is terminated. 

They help us store and process intermediate results. Temporary tables are very useful we there is a situation whereby we only need to store temporary data. 

How to use a temporary table in SQL

So, Now let us see how a temporary table is being created.

1. CREATE TABLE #student(id INT, name VARCHAR(25), department,VARCHAR(20));

This single line of code above creates a table in the database with the fields which are the columns, id of type integer, name of type varchar which should not be more than 25 characters, And department column too must not be more than 20 characters. 

This is not really different from the persistence table, the only little difference in it is the hashtag(#). You have to place the hashtag before the table name. Now, we have created a temporary table. we can now insert values into it.

1. INSERT INTO #student VALUES (01, 'Cindy', 'Law'), (02, 'Smith', 'Medicine')

Now, Let's query the table. below is how to do that. Assuming I want all the values in the table.

1. SELECT * FROM #student

 so the query returns all data in the student table.

STUDENT TABLE

id     name     department

01    Cindy     law

02    smith     Medicine

There are 2 types of Temporary Table:

1. Global Temporary Table

2. Local Temporary Table

A global temporary table is such a table whereby there is visibility to sessions. This means that table(s) are visible to connections. So, if you create a global temporary table in one session, you can start using it in other sessions.

This means that tables that are available to all sessions and all users. They are dropped automatically when the last session using the temporary table has completed. A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name).

1. CREATE TABLE ##student(id INT, name VARCHAR(25), department,VARCHAR(20));

The only difference in the syntax of creating this table and the previously created above is the hash-tag. for the local temporary table, it is just one hash-tag for the local table while for the global temporary table it is two hashtag.

So, The same thing applies in inserting values to it and querying it. Just that the hashtag that precedes the name of the table must be double.

The local temporary table  has been dealt with above. that was the first table that was created with one hashtag

A few things to note in the global temporary table and local temporary table are:

A global temporary table is created using CREATE TABLE statement with the table name prefixed with a double number sign (##table_name).

But For a local temporary table, when you are creating it you need a single hashtag (#) then followed by the name of the table (#table_name). table it is two hashtags

When to use a temporary table in SQL, do we really need it?

Temporary tables are very useful if there is a situation whereby we only need to store temporary data. There is no point in putting data that you know is not permanent in a regular table. And since a temporary table can be created and used, it is very okay to make use of that. So you don’t have to put temporary data in a permanent table.

 Difference between Temporary Table and Persistent table

Having explained what a temporary table is, how to use it and when to use it. Now we shall be seeing the difference between a temporary table and a persistent table. Another name for a persistent table is a regular table. In a regular or a persistent table it exists permanently until you yourself drop the table
The temporary tables could be very useful in some cases to keep temporary data. 

The most important thing that should be known about temporary tables is that they will be deleted when the current client session terminates. As stated earlier, temporary tables will only last as long as the session is alive. For instance, If you run the code in a script, the temporary table will be destroyed automatically when the script finishes executing.

That's all about what is temporary tables in SQL. We have seen both temporary and persistence table as well local and global temporary table. The temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be known for temporary tables is that they will be deleted when the current client session terminates. As stated earlier, temporary tables will only last as long as the session is alive. 

If you run the code in a script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Other SQL Tutorials and Examples for Further Learning

• How to join three tables in one single SQL query (solution)
• Write a SQL query to find all table names on a database in MySQL (solution)
• 4 Free Books to learn Microsoft SQL Server database (books)
• Top 5 Websites to learn SQL online for FREE? (websites)
• 5 Free Courses to learn Database and SQL (free courses)
• Difference between clustered and non-clustered indexes in SQL? (answer)
• Difference between Primary and Candidate key in the table? (answer)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between the Unique and Primary keys in the table? (answer)
• 5 Courses to learn Database and SQL Better (courses)
• The real difference between WHERE and HAVING clause in SQL? (answer)
• 5 Courses to learn Oracle and Microsoft SQL Server database (courses)
• Difference between Self and Equi Join in SQL? (answer)
• 5 Free Courses to learn Oracle and SQL Server? (courses)
• Top 5 Courses to learn Microsoft SQL Server in-depth (courses)
• How to migrate SQL queries from Oracle to SQL Server? (answer)
• Top 5 Websites to learn SQL online for FREE? (resource)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• Write a SQL query to copy or backup a table in MySQL (solution)
• How do you find the duplicate rows in a table on a database? (solution)
• 5 Free Courses to learn T-SQL and SQL Server for Beginners (Courses)
• Difference between Primary and Foreign keys in the table? (answer)

Thanks for reading this article so far.  If you like this article and my explanation and example of temporary table then please share with your friends and if you have any questions which don't know answer or any doubt feel free to ask in comments. 

P. S. - If you are new to SQL and database and want to learn Database and SQLin depth, then I highly recommend you check out these best SQL and Database courses, which are both comprehensive and very engaging.