How to work with Temporary Tables in SQL
What is a Temporary Table?
There are RDBMS, which support temporary tables. Temporary tables are a great feature that lets you store and process intermediate results by using the same selection, update and join capabilities that you can use with typical SQL Server Table.
Most common scenario’s for using Temporary Tables are :
→ You may need temporary staging tables for reports that visit several databases. At the moment I have a bunch of reports that visit about several dozen database instances of several different types. Initial queries run on each database, fetch data into temporary tables in the reporting database and final report query run on their temporary tables.
This “reporting structure” would be very hard to code without using temporary tables.
→ Similar to the above, you may temporarily need data from one database in order to run a query in a second database. To do this, it’s often easiest to dump the data from the first database, load it to a temporary table in the second database and then run the final query in the second table.
→ Replacing subqueries with joins MySQL doesn’t do some types of constant well, so running the constant subquery into a temporary table and using a join instead can produce big performance gain.
How to CREATE a Temporary Table?
→ SQL Server provided two ways to create a Temporary Tables
The pound sign (#) before the table’s name is the identifier that SQL Server uses to flag a temporary table.
→ MySQL used temporary keyword to create a Temporary table
Operations which can be performed on Temporary Table :
Each user would essentially receive a private table for his or her own use. Each user could update, insert and delete records from the table without the tables' data.
For the basic SQL operations refer:
How to DROP a Temporary Table?
→ For SQL Server
→ For MySQL
Types of Temporary Tables in SQL :
→ Local Temporary Table -
A Local Temporary Table is available only for the session that has created it. It is automatically dropped (deleted) when the connection that has created it, is closed.
Example:
→ Global Temporary Table -
To create a Global Temporary Table add the “##” symbol before the table name.
Example:
