MySQL Temporary Table

Do you want to store the intermediate results of your query in some table but only for a given DB session and not persist for the lifetime? Is there some way to hold temporary data for a short time without making it permanent on the database forever? If you have ever thought about this and wondered how to do that, then you have arrived at the right place. In this article, we will take a brief look at how to store intermittent data temporarily.

As the name suggests, Temporary Tables are used when we need to store data temporarily. A Temporary Table is a special kind of table that is not stored in the DB but persists while the current database session is active in which that temporary table was created. The Temporary Tables help us to store and process intermediate results.

In this article, we will look at what a Temporary Table is, how to create and insert data, and how to drop temporary tables along with their syntax with the help of various examples.

MySQL Temporary Table

A temporary table in MySQL is a table that allows one to store a temporary result set of a query and which one can reuse multiple times during one session. Temporary tables are only available to the current ongoing session and are dropped automatically when the current session is terminated. A temporary table is helpful in cases where a SELECT statement is expensive to query the data (as it may involve complex and multiple joins on tables such that every table contains a vast amount of data. So, one can use the temporary table to store the result and then another query to process this data.

How to Create a Temporary Table?

The syntax to create a temporary table is very similar to the syntax for creating any normal table. Instead of the CREATE TABLE, which creates a normal table, we add the TEMPORARY keyword, so, eventually, we use the CREATE TEMPORARY TABLE clause to create a temporary table.

Syntax:

CREATE TEMPORARY TABLE <temporary-table_name>

(

<column1> <datatype>,

<column2> <datatype>,

<column3> <datatype>,

………………..,

<columnN> <datatype>

table_constraints like PRIMARY KEY, FOREIGN KEY

);

Examples of MYSQL Temporary Table

In this example, we are going to talk about MySQL Temporary Table. First of all, let’s create a Temporary table named StudentDetailsTemp.

Example:

CREATE TEMPORARY TABLE StudentDetailsTemp(
  id int PRIMARY KEY, 
  name varchar(255),
  gender char(1),
  age int
);

Output:

CREATE TEMPORARY TABLE

Now, to see the structure of the temporary table created above, we can use the DESC keyword just like how you see the structure of the normal table.

DESC StudentDetailsTemp;

DESCRIBE TABLE

How to Create a Temporary Table Whose Structure is Based on a Normal Table?

To create a temporary table whose structure is based on a normal table, we cannot simply use the CREATE TEMPORARY TABLE statement. Instead, we can do it in the following way:

Syntax:

CREATE TEMPORARY TABLE <temporary-table_name>SELECT * FROM <normal_table>LIMIT 0;

Example:

Let’s us assume that our database has a normal table named StudentDetails with four columns: id, name, gender, and age. We want to create a temporary table that has the same four columns. We can do it as follows:

CREATE TEMPORARY TABLE StudentDetailsTemp
SELECT * FROM StudentDetails
LIMIT 0;

Output:

CREATE TEMPORARY TABLE

Now to see the structure of the temporary table created above, we can use the DESC keyword just like how you see the structure of the normal table.

DESC StudentDetailsTemp;

DESCRIBE TABLE

How to Insert Data into a Temporary Table?

1. Insert Sata Using INSERT Statement

The syntax to insert into the temporary table is exactly the same as any normal table.

Syntax:

INSERT INTO <temporary_table_name>(<column1>,<column2>,………..)

VALUES(<value1>,<value2>,……….);

Example:

Insert the details of female students from the StudentDetails table into a temporary table named StudentDetailsTemp created above.

INSERT INTO StudentDetailsTemp(id, name, age, gender) VALUES(1, 'Rachel', 23, 'F');
INSERT INTO StudentDetailsTemp(id, name, age, gender) VALUES(2, 'Monica', 28, 'F');
INSERT INTO StudentDetailsTemp(id, name, age, gender) VALUES(3, 'Phoebe', 26, 'F');

Output:

INSERT TEMPORARY TABLE

To view the data inserted above, we can use the SELECT clause to view the data in StudentDetailsTemp.

SELECT * FROM StudentDetailsTemp;

Output:

SELECT TEMPORARY TABLE

2. Insert Data From the Data in the Normal Table

You can insert into temporary table by using SELECT statement on the normal table.

Syntax:

INSERT INTO <temorary_table_name>(<column1>,<column2>,………..)SELECT <column1>,<column2>,……FROM <normal_table_name>;

Example:

Insert the details of female students from the StudentDetails table into a temporary table named StudentDetailsTemp created above.

INSERT INTO StudentDetailsTemp(id, name, gender, age) 
SELECT id, name, gender, age
FROM StudentDetails 
WHERE gender = 'F';

Output:

INSERT TEMPORARY TABLE – SELECT FROM NORMAL TABLE

To view the data inserted above, we can use SELECT clause to view the data in StudentDetailsTemp.

SELECT * FROM StudentDetailsTemp;

Output:

SELECT TEMPORARY TABLE

How to DROP Temporary Table?

You can drop the temporary table just like how we drop a normal table using the DROP TABLE <table_name> statement. But, this way, there is a risk of dropping the normal table instead of the temporary table, which has the same name as the normal table.

To drop the temporary table, you can use the DROP TEMPORARY TABLE <table_name> statement, which removes a temporary table named <table_name> only, but not a regular table.

Moreover, even if you do not drop the temporary table manually by running the query, it is dropped automatically once your current session is closed.

Syntax:

DROP TEMPORARY TABLE <table_name>;

Example:

Let us drop the above created temporary table named “StudentDetailsTemp”.

DROP TEMPORARY TABLE StudentDetailsTemp;

Output:

DROP TEMPORARY TABLE

Conclusion

So, Temporary Tables are used when we need to store data temporarily. They help by storing the intermittent results of queries which can be used to further process the data. Temporary tables are only available to the current ongoing session and are dropped automatically when the current session is terminated. In this article, we took a brief look at understanding what a Temporary Table is, the syntax to create and drop temporary table and insert data into temporary table.