Home » Core Java » sql » SQL Temp Table Example

About Yatin

Yatin
The author is graduated in Electronics & Telecommunication. During his studies, he has been involved with a significant number of projects ranging from programming and software engineering to telecommunications analysis. He works as a technical lead in the information technology sector where he is primarily involved with projects based on Java/J2EE technologies platform and novel UI technologies.

SQL Temp Table Example

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.

SQL Temp Table - Temporary table & Displaying data
Fig. 1: Creating a Temporary table & Displaying data

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.

SQL Temp Table - Temporary table from SQL SELECT query
Fig. 2: Creating a Temporary table from SQL SELECT query

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.

Download
You can download the full source code of this example here: SQL Temp Table Example
(No Ratings Yet)
Start the discussion Views Tweet it!

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Leave a Reply

avatar
  Subscribe  
Notify of