How to Create a Temporary Table in MySQL

1. Create Basic Temporary Table in MySQL
2. Creating Temporary Table From SELECT Query

In this tutorial, we aim at exploring different methods to create a temporary table in MySQL.

One of the key features of a temporary table is that it is instrumental in storing provisional data. This feature is enabled in MySQL version 3.23 and above.

These tables are lost when the user manually drops the table or the session ends.

Another feature of a temporary table is that the same name of the table can be used in multiple connections. This is made possible as the client is only able to work the temporary table created by them.

There are two main ways to create a temporary table in MySQL:

• Basic temporary table creation.
• Temporary table creation from SELECT query.

However, before we begin, we create a dummy dataset to work with. Here we create a table, student_details, along with a few rows in it.

-- create the table student_details
CREATE TABLE student_details(
  stu_id int,
  stu_firstName varchar(255) DEFAULT NULL,
  stu_lastName varchar(255) DEFAULT NULL,
  primary key(stu_id)
);
-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The above query creates a table along with rows with student first name and last name in it. In order to view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above code would give the following output:

stu_id	stu_firstName	stu_lastName
1	      Preet	        Sanghavi
2	      Rich	        John
3	      Veron	        Brow
4	      Geo	        Jos
5	      Hash	        Shah
6	      Sachin	    Parker
7	      David	        Miller

Now, let us create a temporary table with the name students_temporary similar to the student_details table.

Create Basic Temporary Table in MySQL

One of the most basic methods to create a temporary table is by using the TEMPORARY keyword. We can create a temporary table named students_teporary as follows:

-- Basic temporary table creation
CREATE TEMPORARY TABLE students_teporary(
      stu_id int,
      stu_firstName varchar(255) DEFAULT NULL,
      stu_lastName varchar(255) DEFAULT NULL,
      primary key(stu_id)
    );

The code above creates a temporary table named students_temporary. Next, let us insert a few entries in this table with the following code:

-- insert rows to the table student_details
INSERT INTO student_details(stu_id,stu_firstName,stu_lastName) 
 VALUES(1,"Preet","Sanghavi"),
 (2,"Rich","John"),
 (3,"Veron","Brow"),
 (4,"Geo","Jos"),
 (5,"Hash","Shah"),
 (6,"Sachin","Parker"),
 (7,"David","Miller");

The output of the aforementioned code results in a temporary table as follows:

stu_id	stu_firstName	stu_lastName
1	        Preet	      Sanghavi
2	        Rich	      John
3	        Veron	      Brow
4	        Geo	          Jos
5	        Hash	      Shah
6			Sachin		  Parker
7			David	      Miller

Creating Temporary Table From SELECT Query

One other method of creating temporary tables is by making use of the select statement. This method helps us replicate an entire table into a temporary table with the same entities and data types. Let us try to create a temporary table students_details_temporary by using the SELECT statement. We can do this with the following code.

-- Replicating the students_details table
CREATE TEMPORARY TABLE IF NOT EXISTS students_details_temporary AS (SELECT * FROM students_details);

The aforementioned code will give the following output:

stu_id	stu_firstName	stu_lastName
1	        Preet	      Sanghavi
2	        Rich	      John
3	        Veron	      Brow
4	        Geo	          Jos
5	        Hash	      Shah
6			Sachin		  Parker
7			David	      Miller

As we can see, a temporary table with the same entities and entries as that of the original table (student_details) has been generated.

Thus, with the help of the above two methods, we can efficiently create a temporary table. This temporary table is deleted as soon as the very last connection is terminated.