Wikibooks: Structured Query Language/Temporary Table

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/Structured_Query_Language/Temporary_Table

Nav Regular tables are containers to store data for a shorter or longer time periods and to make the data available to various processes. In contrast sometimes there is the requirement to handle data for a short time and only for local purposes. The provision of TEMPORARY TABLES accomplishes this. They are subject to the SQL syntax in the same way as regular tables. The common characteristic of all temporary tables is that every session (connection) gets its own incarnation of the temporary table without any side effect to other sessions. This leads to the situation that every session sees only those data which it has inserted previously. The data is not shared between different sessions even if they use the same table name at the same time. Every session works on a different incarnation. A second common characteristic is that with the termination of the session all data of the temporary table is thrown away automatically. An explicit DELETE or DROP TABLE is not necessary. The concept of temporary tables is similar to the concept of arrays of records within programming languages. The advantage over arrays is the availability of all DML statements known in SQL eg. if you need some intermediate data you can retrieve it from a regular table and store it in a temporary table with one single Insert+Subselect command. The advantage over regular tables is that the overhead of logging and locking might be saved. There are three slightly different types of temporary tables Global temporary tables Local temporary tables Declared local temporary tables. CREATE GLOBAL TEMPORARY TABLE (.) [ ON COMMIT { PRESERVE DELETE } ROWS ] CREATE LOCAL TEMPORARY TABLE (.) [ ON COMMIT { PRESERVE DELETE } ROWS ] DECLARE LOCAL TEMPORARY TABLE (.) [ ON COMMIT { PRESERVE DELETE } ROWS ] If the phrase ON COMMIT DELETE ROWS is used the data is automatically thrown away with every COMMIT command else at the end of the session (or with a DELETE command). = Global Temporary Tables (GTT) = If a GTT is created its definition becomes part of the ...