SQL Temporary Tables

Ratings:
(4)
Views:300
Banner-Img
  • Share this blog:

SQL Temporary Tables Overview

There are RDBMS which support temporary tables. Temporary Tables are a great feature that  lets you store and process intermediate results by using the same selection, update, and join capabilities that you can use with typical SQL Server tables. The SQL temporary tables could be very useful in some cases to keep temporary data. The most important thing that should be knows for temporary tables is that they will be deleted when the current client session terminates. Temporary tables are available in MySQL version 3.23 onwards. If you use 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 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 the MySQL client program, then the temporary table will exist until you close the client or manually destroy the table.

Creating Temporary Tables in SQL with Example

Here is an example showing you usage of temporary table: mysql> CREATE TEMPORARY TABLE SALESSUMMARY ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SALESSUMMARY -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber     |      100.25 |           90.00 |                 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) When you issue a SHOW TABLES command then your temporary table would not be listed out in the list. Now if you will log out of the MySQL session and then you will issue a SELECT command then you will find no data available in the database. Even your temporary table would also not exist.

Dropping Temporary Tables

By default all the temporary tables are deleted by MySQL when your database connection gets terminated. Still you want to delete them in between then you do so by issuing DROP TABLE command. Following is the example on dropping a temproary table. mysql> CREATE TEMPORARY TABLE SALESSUMMARY ( -> product_name VARCHAR(50) NOT NULL -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00 -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00 -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0 ); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO SALESSUMMARY -> (product_name, total_sales, avg_unit_price, total_units_sold) -> VALUES -> ('cucumber', 100.25, 90, 2); mysql> SELECT * FROM SALESSUMMARY; +--------------+-------------+----------------+------------------+ | product_name | total_sales | avg_unit_price | total_units_sold | +--------------+-------------+----------------+------------------+ | cucumber     |      100.25 |           90.00 |                 2 | +--------------+-------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE SALESSUMMARY; mysql>  SELECT * FROM SALESSUMMARY; ERROR 1146: Table 'TUTORIALS.SALESSUMMARY' doesn't exist  

You liked the article?

Like : 0

Vote for difficulty

Current difficulty (Avg): Medium

About Author
Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.


Stay Updated


Get stories of change makers and innovators from the startup ecosystem in your inbox