Disclosure: This article may contain affiliate links. When you purchase, we may earn a commission.

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). 

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. 

temporary table in database

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. 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.

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

In Conclusion, 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 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)

No comments:

Post a Comment

Feel free to comment, ask questions if you have any doubt.