What are Temporary Tables?

Temporary tables are pretty much what their name describes: they are the tables which are created in a database to store temporary data. We can perform SQL operations similar to the operations on permanent tables like CREATE, UPDATE, DELETE, INSERT, JOIN, etc. But these tables will be automatically deleted once the current client session is terminated. In addition to that, they can also be explicitly deleted if the users decide to drop them manually.

Various RDBMS, like MySQL, support temporary tables starting from version 3.23 onwards. If you are using an older version of MySQL than 3.23, you can”t use temporary tables, but you can use heap tables.

As stated earlier, temporary tables will only last as long as the client session is alive. If you run the code in a PHP script, the temporary table will be destroyed automatically when the script finishes executing. If you are connected to the MySQL database server through a MySQL client program, then the temporary table will exist until you close the client connection or manually destroy the table.

Creating Temporary Tables in MySQL

To create temporary tables in MySQL, we follow the same query as creating regular database tables. However, instead of using the CREATE TABLE statement, you use CREATE TEMPORARY TABLE statement.

Syntax

Following is the syntax to create a temporary table −

CREATE TEMPORARY TABLE table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example

Following is the SQL Query to create a temporary table in MySQL database −

CREATE TEMPORARY TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Just like normal tables you can insert data into a temporary table using the INSERT statement. Following query inserts 3 records into the above created temporary table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 );

The temporary table CUSTOMERS will be created and will have following records −

When you issue a SHOW TABLES command, then your temporary table will not be displayed in the list of tables. To verify whether the temporary table is created you need to retrieve its data using the SELECT statement. Since all the temporary tables will be removed when the current session is closed, if you log out of the MySQL session and then issue a SELECT command, you will not find temporary table in the database.

Dropping Temporary Tables in MySQL

Though all the temporary tables are deleted by MySQL when your database connection gets terminated, still, if you want to delete them manually, then you can do so by issuing a DROP TEMPORARY TABLE command.

Syntax

Following is the basic syntax to delete a temporary table:

DROP TEMPORARY TABLE table_name;

Example

Following query drops the temporary table CUSTOMERS created in the previous example −

DROP TEMPORARY TABLE CUSTOMERS;

Verification

Since we have removed the temporary table CUSTOMERS, if you try to retrieve the contents of it using the SELECT statement, it will generate an error saying the table does not exist.

SELECT * FROM CUSTOMERS;

This will produce following result −

ERROR 1146: Table ''TUTORIALS.CUSTOMERS'' doesn''t exist

Temporary Tables in SQL Server

The temporary table created in MySQL is visible only within the current session. But, in Microsoft SQL Server you can create two types of temporary tables.

• Local Temporary Tables: A Local Temporary Table is accessible only in the session that has created it. It is automatically deleted when the connection that has created it gets closed. If the Temporary Table is created inside the stored procedure, it get dropped automatically upon the completion of stored procedure execution.

• Global Temporary Tables: Global Temporary Tables are visible to all connections and Dropped when the last connection referencing the table is closed.

Syntax of the Local Temporary Tables

To create Local Temporary Table in SQL Server a single # is used as the prefix of a table name, as shown below −

CREATE TABLE #table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example of the Local Temporary Tables

Following query creates a Local temporary table named CUSTOMERS in the SQL server −

CREATE TABLE #CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Syntax of the Global Temporary Tables

To create a Global Temporary Table, we need to add the prefix ## before the table name, as shown below −

CREATE TABLE ##table_name(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....
   columnN datatype,
   PRIMARY KEY( one or more columns )
);

Example of the Global Temporary Tables

Following query creates a Global temporary table named Buyers in the SQL server −

CREATE TABLE ##Buyers(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Dropping Temporary Tables in SQL Server

If you want to drop a temporary table in SQL Server manually, you need to execute the DROP TABLE statement by placing # before the local temporary table name and ## before the global temporary table name.

Example

Following query removes the Local temporary table Customers created in the previous example.

DROP TABLE #Customers;

Whereas, following query removes the global temporary table Buyers.

DROP TABLE ##Buyers;