What is temporary table? 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.

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.