MySQL – Beginning Temporary Tables in MySQL

MySQL supports Temporary tables to store the resultsets temporarily for a given connection. Temporary tables are created with the keyword TEMPORARY along with the CREATE TABLE statement.

Let us create the temporary table named Temp

CREATE TEMPORARY TABLE TEMP
(id INT);

Now you can find out the column names using DESC command

DESC TEMP;

The above returns the following result

MySQL - Beginning Temporary Tables in MySQL temptable1 

This table can be accessed only for the current connection and it can be used like a permanent table and automatically dropped when the connection is closed. However, you can not find temporary tables using INFORMATION_SCHEMA. TABLES system view. It will only list out the permanent tables.

MySQL usually stores the data of temporary tables in memory and processed by Memory Storage engine. But if the data size is too large MySQL automatically converts this to the on – disk table and use MyISAM engine.

You can also create a permanent table with the same name of a temporary table in the same connection. However the structure of permanent table is visible only if the temporary table with the same name is dropped.

Let us create a permanent table with the same name Temp as below

CREATE TABLE TEMP
(id INT,
names VARCHAR(100));

Now running the following command stills gives you the structure of the temporary table temp created earlier.

DESC TEMP;

MySQL - Beginning Temporary Tables in MySQL temptable1

You can drop the temporary table using DROP TEMPORARY TABLE command;

DROP TEMPORARY TABLE TEMP;

After you executed the temporary table, run the following command

DESC TEMP;

Now you will see the structure of the permanent table named temp

MySQL - Beginning Temporary Tables in MySQL temptable2 

In summary – If there is a Temporary Table in MySQL it gets first priority over the permanent table in the session.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Previous Post
SQL SERVER – SSIS Look Up Component – Cache Mode – Notes from the Field #028
Next Post
Developer’s Life – Every Developer is a Spiderman

Related Posts

4 Comments. Leave new

  • Ritesh Khatri
    June 13, 2014 5:15 pm

    CREATE TEMPORARY TABLE TEMP
    (id INT);

    Error Thrown From This Command :

    Msg 343, Level 15, State 1, Line 1
    Unknown object type ‘TEMPORARY’ used in a CREATE, DROP, or ALTER statement.

    Reply
    • It works with MySQL only as mentioned two times in Title.

      Are you using MySQL or SQL Server?

      Reply
      • Ritesh Khatri
        June 16, 2014 2:49 pm

        ok sir. sorry for reading mistake.. have any option for sql server 2008 R2

      • Hello, Under MsSQL The @ sign in used to create temp tables. A single @ creates a table with scope under the current connection. Two @@ create a temp table with scope throughout the database. As such it must be created at db start time. I’m unsure of the process but should be found somewhere on the net.

Leave a Reply

Menu