Temporary Tables in SQL

In SQL, temporary tables are used to hold the backup of table temporarily, or store intermediary result in a table. These kinds of tables are used only in the session and its use is no more required by other users or in the next session. Such tables can be created like below. It is the temporary table, available only in the current session and it disappears when we close and reopen the session. These tables act as a normal table throughout the session.

CREATE GLOBAL TEMPORARY TABLE BOOK_LOAN_MAY2015 (
       ISBN NUMBER (13),
       CP_NUMBER NUMBER (3),
       LOAN_DATE DATE);

We can insert the records into this temporary table using INSERT statement.

INSERT INTO BOOK_LOAN_MAY2015 VALUES (82014300, 1, ’20-May-2015’);

We can use queries to pull the records from some other table to insert the records into temporary table.

INSERT INTO BOOK_LOAN_MAY2015
SELECT bl.ISBN, bl.BOOK_CP_NUMBER, bl.LOAN_DATE FROM BOOK_LOAN bl
WHERE TO_CHAR (LOAN_DATE, 'MONYYYY') = 'MAY2015';

One can query these tables like we query normal tables. We can have WHERE, ORDER BY, GROUP BY clauses in the query.

SELECT * FROM BOOK_LOAN_MAY2015;

These tables are dropped automatically once the session is closed. However if we need to drop these tables in the current session then we can use DROP statement to drop this table.

DROP TABLE BOOK_LOAN_MAY2015;