Welcome readers, in this tutorial, we will learn how to use the temporary tables in the database.
1. Introduction
A temporary table is a table that,
- Is only visible to the current session
- Is automatically dropped when the current session is closed
- Is permanently not stored in a database; therefore, it is effective for the testing purpose
- Is only available and accessible to the client that creates it
To start with this tutorial, we are hoping that users at present have their preferred database installed on their machines. For easy usage, I am using MySQL on a Windows operating system. If someone needs to go through the MySQL installation, please watch this video.
2. SQL Temp Table Example
The following tutorial will illustrate the different approaches for creating a temporary table and dropping it.
2.1 Creating a Temporary Table
Programmers can use the following script to create a temporary table named – cashback in a database named – sql_temp_table_example and add some sample data to it.
Query 1
01 02 03 04 05 06 07 08 09 10 11 12 13 14 | /* QUERY 1 = CREATING A TEMPORARY TABLE AND ADDING RECORDS INTO IT. */CREATE TEMPORARY TABLE cashback( id INT AUTO_INCREMENT, customer_id INT NOT NULL, cashback_amount DEC(50, 2), allotted_for_order_id INT NOT NULL, primary key (id));INSERT INTO cashback (customer_id, cashback_amount, allotted_for_order_id) VALUES (205, 30.2, 8312);INSERT INTO cashback (customer_id, cashback_amount, allotted_for_order_id) VALUES (907, 130.2, 8474);INSERT INTO cashback (customer_id, cashback_amount, allotted_for_order_id) VALUES (46, 0.2, 3570);SELECT * FROM cashback; |
If everything goes well, the cashback table will be created, and the data will be displayed.
2.2 Creating a Temporary Table based on Query Example
Programmers can use the following script to create a temporary table named – cashback_bkp from the SQL SELECT query.
Query 2
/* QUERY 2 = CREATING A TEMPORARY COPY OF THE ORIGINAL TABLE AND DISPLAY RESULTS. */ CREATE TEMPORARY TABLE cashback_bkp SELECT * FROM cashback; SELECT * FROM cashback_bkp;
If everything goes well, the cashback_bkp table will be created, and the data will be displayed.
2.3 Dropping a Temporary Table
Programmers can use the SQL DROP command to delete a temporary table.
Query 3
/* QUERY 3 = DROPPING TEMPORARY TABLE. */ DROP TEMPORARY TABLE cashback;
Make note, to add the TEMPORARY keyword. This query only removes a temporary table but not permanent and helps developers to avoid the mistake of dropping a permanent table when the temporary table name is the same as permanent.
That is all for this tutorial and I hope the article served you whatever you were looking for. Happy Learning and do not forget to share!
3. Conclusion
In this section, developers learned how to create and drop a temporary table in the database. Developers can download the sample scripts in the Downloads section.
4. Download the SQL Script
This was an example of creating/dropping a temporary table in the database.
You can download the full source code of this example here: SQL Temp Table Example
