SQL Temporary Tables

By | September 13, 2020
SQL Temporary Tables

Some Relational Database Management Systems(RDBMS), support temporary tables. A Temporary Table is similar to the normal tables present in the Database, like the normal table the temporary table support all the SQL commands, the only difference is the temporary table get deleted as soon as the session of the user or table gets ended.

SQL Temporary Tables

Temporary tables often used in web pages when we want to create a table for a session or a period of time, the only purpose of a temporary table to keep the temporary data which is only required for a particular period of time.

Vamware

Syntax

CREATE TEMPORARY TABLE table_name(
column_1 datatype,
column_2 datatype,
.............);

Example

CREATE TEMPORARY TABLE Bill
( product VARCHAR(50) NOT NULL,
price Decimal NOT NULL,
payment_method VARCHAR(10) DEFAULT 'Cash');

Insert Data in Temporary Variable

INSERT INTO Bill VALUES("Bread", 50.0,'Cash');

Show the table data

SELECT * FROM Bill;
+---------+-------+----------------+
| product | price | payment_method |
+---------+-------+----------------+
| Bread   |    50 | Cash           |
+---------+-------+----------------+

Delete Temporary Table

If we create a temporary table using a scripting language Like PHP then the table gets deleted automatically once the code finishes executing, but if we have connected to the MySQL database server then the table will remain till client close the program or manually destroy the table.

The MySQL Engine will automatically delete the table once you exit your RDBMS software or terminate the connection but if you want to delete the table in between then using the DROP command we can delete the temporary table before the session gets ends.

Example

Query: Delete the temporary table Bill.

DROP TABLE Bill;
Query OK, 0 rows affected (0.05 sec)

Summary

  • To create a Temporary table we use the TEMPORARY clause
  • Temporary tables are used to hold temporary data.
  • We can perform all the SQL operations on the Temporary table.
  • The table gets deleted as soon as the connection or software get terminated.

Leave a Reply

Your email address will not be published. Required fields are marked *