How to use the SELECT INTO TEMP table statement in MySQL

Sometimes, we are supposed to fetch out data from different MySQL tables which we can use for some specific purpose. For our ease, we create a temporary table and place all the data there which we can later use. When the session is over, the table will vanish on its own, else it can delete it using the DROP command. Similarly, to create the clone of the table we use the statement “SELECT INTO”, which not only copies the contents of the selected table but also inserts all the content into the newly created table.

In this write-up, we will learn how to use the statement “SELECT INTO TEMP TABLE” in MySQL and how this statement works.

What is Select into temp table

According to the discussion above, we know the statement “SELECT INTO TEMP TABLE” is a combination of two different statements that have their own separate function, by using this statement we can execute multiple functions using a single statement. The functions that can be performed with the above statement are:

• Creating a new temporary table
• Making a clone of the existing table
• Reading its file
• Inserting its all file into the newly created temporary table

The general syntax of using this statement is:

But this syntax is applicable in SQL only not in MySQL, but we can have the same results of the above statement in MySQL in other ways.

How to insert data in a temporary table using MySQL?

To copy data from any existing table into the temporary table in MySQL, we should first create a temporary table, named, temporary_Data, using clause “TEMPORARY TABLE” and also define columns of the table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-01.png" data-lazy- height="124" src="data:image/svg xml,” width=”904″>

To display all the tables of the database, use:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-02.png" data-lazy- height="432" src="data:image/svg xml,” width=”718″>

The created table is not in the list of tables which confirms the table is temporary, now to display the temporary table, we use:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-03.png" data-lazy- height="170" src="data:image/svg xml,” width=”756″>

The output is showing “Empty set” because there is no data inserted in the table, but it confirmed the existence of the temporary table. To copy the entire data of any existing table having the same number of columns, we will first insert the data into a temporary table by using “INSERT INTO”  and then select the existing table from where we are supposed to copy the data.

The general syntax would be like this:

Following the general syntax, we will copy the data from the existing table, named, Guys into the newly created temporary table, named, “temporary_data”.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-04.png" data-lazy- height="160" src="data:image/svg xml,” width=”904″>

To display the temporary table,

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-05.png" data-lazy- height="410" src="data:image/svg xml,” width=”714″>

All the data of table “Employee_data” has been copied in the temporary table “temporary_Data”.  Now if we want to copy and paste the data of a specific column, let’s say, we want to copy “id” from the existing table “Grocery_bill” to the column “ids” of the existing temporary table, “temporary_Data”, we will run the following statements.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-06.png" data-lazy- height="162" src="data:image/svg xml,” width=”904″>

To view the temporary table:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-07.png" data-lazy- height="444" src="data:image/svg xml,” width=”704″>

From the output, we can observe the column from the table “Grocey_bill” has been copied and pasted in the column of the temporary table where “NULL” is in the columns next to the new entries showing there are no values in them. Hence we can copy the entire column as well as any specific columns to the new table.

We can also delete the temporary table by using the clause “DROP TEMPORARY TABLE”, for example, if we want to delete the temporary table, named, “temporary_Data”, we use:

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/mysql-select-into-temp-table-08.png" data-lazy- height="198" src="data:image/svg xml,” width=”840″>

The temporary table has been deleted successfully.

Conclusion

To extract different data from different tables is slightly easy if we collect all the data in a temporary table. This write-up assists us in understanding that we can create the temporary table by using the clause  “TEMPORARY TABLE” and can copy either the entire data or some specific column from The existing table into the temporary table.