In this article, we are going to learn how to create a temp table and further drop these tables. The temporary tables are session-specific tables that are created within the session. When the session closes, the table is dropped by itself. A temporary table exists within a session only; therefore, a table created by one session is not visible to another session. For example, Session ID 501 has created a temporary table then session ID 502 cannot access it or manipulate it. Two different sessions can have a temporary table with the same name.

The temporary tables are useful when you want to use a small subset of the large table, and it is being used multiple times within a stored procedure. In such cases, instead of applying the filter on the table multiple times, you can save the subset of large tables in a temporary table and use it within a stored procedure.

Important facts of the temporary tables:

1. MySQL Temporary table has a decoupled relationship with the database schema, which means that when we drop the MySQL database, it does not necessarily drop the temporary tables within the database
2. The InnoDB database engine does not support the compressed, temporary table. If you had enabled the InnoDB_strict_mode parameter, then it will return an error. If you disabled the InnoDB_strict_mode parameter, MySQL will issue a warning and create a table with a non-compressed row format
3. To create a temporary table within the stored procedure, the user must have create temporary table privilege. Once it is granted, then to update, delete, or insert the data within that temporary table does not require any special privileges. There is one implication of this behavior. Suppose the stored procedure that uses a temporary table and a USER 1 who had developed the procedure has the create temporary table privilege. Now, USER 2 tries to execute it, but it does not have the create temporary table permission. In such cases, MySQL uses the privileges of the USER 1 to create the temporary table. Once the procedure completes successfully, the privileges will be reverted
4. You cannot create a temporary table based on the definition that the table already has, and it resides on MySQL tablespace, a general tablespace, or InnoDB tablespace. To create a temporary table based on the definition of another table, you must use the following syntax
   1	Select temporary table.. select * from

5. Unlike regular create table statements, the Create temporary table statement does not cause the implicit commit
6. The temporary table can have the same name as the MySQL regular table has. For example, even though a table named employee exists on the database, you can create a temporary table named employee in the database. In such cases, the MySQL table becomes inaccessible. Once the temporary table is dropped, the physical table can be accessed again. So it is always a good practice to create a temporary table with some unique name or make sure that the application drops the temporary table immediately after its purpose is completed

MySQL Temporary table examples

The syntax to create a temp table is as follows:

As you can see, the syntax to create a temporary table is the same as the syntax of creating a MySQL table. The only difference is that you must specify the temporary keyword between create and table keywords. This is very important because if you forget to add the keyword, it creates a regular MySQL table.

Now, let us walk through some examples of the temporary table. I am going to demonstrate the following use cases:

1. Create a temp table and insert data in the table
2. Create a temp table using the output of the select statement
3. Drop the temp table
4. Create a temp table using the structure of another table

Create a temp table and insert data

To create a new temp table named tblemployee on the MySQL database, execute the following query:

Execute the following query to insert the data in the temp table.

Once data is inserted in the temp table, execute the following query to verify that data have been inserted.

The following is the output:

Now, to verify the scope of the tblemployee table, let us run the same query from the MySQL command-line client. To do that, open it, provide a password to connect to the MySQL Server, and execute the following queries:

Output: as you can see, the query has given an error:

Create a temp table using the output of the select statement

Suppose I want to create a temp table using the output of the select query. For example, I want to insert the details of the films table with its category in a temp table named temp_film_details. To do that, execute the following query.

Once details are inserted in the temp table, execute the following query to view the data of the temp table.

Output:

Drop temp Tables

To drop the temp table, we can use the following syntax:

As I mentioned that the on a database a temporary table and an InnoDB table can have same name so make sure you specify the temporary keyword; otherwise, if a table with the same will be dropped

Summary

In this article, we have learned about MySQL temp tables, and the characteristics of it. We also learned how we could create and drop a temp table with different use cases.

Table of contents

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022