JIYIK CN >

In this article, we aim to explore different ways to create temporary tables in MySQL.

One of the main features of temporary tables is that it helps in storing temporary data. This feature is enabled in MySQL 3.23 and later versions.

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

Another feature of temporary tables is that you can use tables with the same name in multiple connections. This is because clients can only process temporary tables that they create.

There are two main ways to create temporary tables in MySQL:

• Basic temporary table creation.
• SELECTCreate a temporary table from a query.

Before we get started, however, we create a dummy dataset to work with. Here, we create a table, student_details, and 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 query above creates a table and rows containing the first and last names of students. To view the entries in the data, we use the following code:

SELECT * FROM student_details;

The above 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

Now, let's create a students_temporarytemporary table called , similar to student_detailsthe table.

Creating a Basic Temporary Table in MySQL

One of the most basic ways to create a temporary table is by using TEMPORARYthe -t keyword. We can create a students_teporarytemporary table named -t 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 above code creates a students_temporarytemporary table named . Next, let's insert some entries into this table using 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 above code is 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

SELECTCreate a temporary table from a query

Another way to create a temporary table is by using the select statement. This method helps us to copy the entire table into a temporary table with the same entities and data types. Let's try SELECTto create a temporary table using the select statement students_details_temporary. 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 above 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 has been generated with student_detailsthe same entities and entries as the original table ( ).

So, with the help of the above two methods, we can effectively create a temporary table. Once the last connection is terminated, this temporary table will be deleted.