How to Create Temporary Table in SQL?
How to Create Temporary Table in SQL
Introduction to Temporary Tables
- Temporary table is a table which is used to store temporary data that can be used further in the same client session.
- By default, temporary table is removed once the client session is terminated.
- Temporary table can be removed explicitly using the statement “DROP TABLE”. This table is accessible only to its creator.
- More than one temporary table can exist in the same session but they must contain different names if they are in the same session. But if the temporary tables are in the different session, then the tables can exist with the same names.
- Temporary tables can have same name as that of normal tables within the same database. If such condition exists, then after the creation of temporary table all the queries executed will now be referencing to the temporary table instead of the normal table. But once this temporary table created with the same name is removed, the normal table is accessible and now the queries will be referencing to the normal table.
- Creating a Temporary table
Syntax:
CREATE TEMPORARY TABLE TABLENAME (column_name1 datatype (size), column_name2 datatype (size), column_nameN datatype (size));
Example:
We already have a database with name “employeedb” and a table with name “employee” in that database. Now we will create a temporary table with same name “employee”.
mysql> USE employeedb; Database changed mysql> CREATE TEMPORARY TABLE employee(Emp_ID INT, Emp_Name VARCHAR(20),Emp_Salary INT); Query OK, 0 rows affected (0.32 sec) mysql> SELECT *FROM employee; Empty set (0.00 sec) mysql> INSERT INTO employee VALUES(1,"Mayuri",45000); Query OK, 1 row affected (0.08 sec) mysql> INSERT INTO employee VALUES(2,"Sakshi",50000); Query OK, 1 row affected (0.04 sec) mysql> SELECT *FROM employee; +--------+----------+------------+ | Emp_ID | Emp_Name | Emp_Salary | +--------+----------+------------+ | 1 | Mayuri | 45000 | | 2 | Sakshi | 50000 | +--------+----------+------------+ 2 rows in set (0.00 sec)
- Now we have created a new table named as “employee” in the database “employeedb”.
- So, just after the creation of temporary table when we are trying to retrieve the data from employee table, we got empty result set. This happens because now the employee table specified in the SELECT query is referencing to the newly created temporary table “employee” and not the existing table “employee”.
- After that when we executed the INSERT query on employee table, then also this query is operated on the temporary table “employee” because we are executing this query after the temporary table creation.
- Drop a temporary table
Syntax:
DROP TABLE TABLENAME;
Example:
Now we will delete a temporary table named as “employee”.
mysql> SELECT *FROM employee; +--------+----------+------------+ | Emp_ID | Emp_Name | Emp_Salary | +--------+----------+------------+ | 1 | Mayuri | 45000 | | 2 | Sakshi | 50000 | +--------+----------+------------+ 2 rows in set (0.00 sec) mysql> DROP TABLE employee; Query OK, 0 rows affected (0.08 sec) mysql> SELECT *FROM employee; +--------+----------+------------+ | Emp_ID | Emp_Name | Emp_Salary | +--------+----------+------------+ | 1 | Mayuri | 40000 | | 2 | Mayuri | 40000 | | 3 | Mayuri | 40000 | | 4 | Mayuri | 40000 | | 5 | Mayuri | 40000 | +--------+----------+------------+ 5 rows in set (0.09 sec)
- When we execute the SELECT query, we are getting two records as an output because we have executed this query after temporary table creation. So, this SELECT query is operated on the temporary table.
- After that we have executed the drop query. Using DROP command, newly created employee table (temporary table) will be dropped.
- Again, when we execute the SELECT query, now it will be operated on our employee table (original table) not the temporary table because the temporary table is already removed from the database.