SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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) 
How to Create Temporary Table in SQL
  • 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) 
How to Create Temporary Table in SQL
  • 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.



ADVERTISEMENT
ADVERTISEMENT