What are temporary tables in SQL?

Temporary tables are a copy of a portion of a larger table to save us time when we need a portion of the table too frequently.

What is the scope of a temporary table?

Temporary tables are accessible during the session in which they were created, and they get automatically destroyed after the session is closed, or they can be deleted manually. But, we can use global temporary tables which are accessible actress different connections.

What are how you can create temporary tables?

We can create temporary tables using two methods-Using select into a statement where directly fetch data from a previously existing table and store it into a temporary table.Or we can first create a temporary table and then copy data into it from other tables.

What are global temporary tables?

Global temporary tables are the ones that have the scope beyond the connection that creates them so that various connections can use them. and its lifecycle is not dependent on anyone's connection, but it remains active until the last connection using it is terminated

Table of contents

Introduction

Syntax

2.1.

Using the select into statement-

2.2.

Create a temporary table using the create table statement-

Lifecycle of temporary table

Global temporary tables

4.1.

syntax

Deletion of temporary tables

5.1.

Automatic deletion

5.2.

Manual deletion

5.2.1.

syntax

Frequently Asked Questions

Conclusion

Last Updated: Mar 27, 2024

Easy

Temporary Tables in SQL

Author Gorakhnath yadav

Do you think IIT Guwahati certified course can help you in your career?

Yes

Introduction

In some scenarios, we require to use the data from a small portion of a table very frequently, but the rest is not used that often. Now it can be very inefficient to filter the same subset of data from the original table when required.

To deal with this scenario, several RDBMS uses the concept of temporary tables. Even though the earlier version didn't have this capability, in MySQL, temporary tables were introduced in version 3.23 for the first time. Temporary tables are tables created temporarily on a database server to store a subset of data from a usual table temporarily. Temporary tables also possess similar operational capabilities as the usual tables. Since we are done with the introduction, it's time to move to learn the syntax for creating temporary tables, their types, and how we can delete temporary tables.

Recommended topics, Coalesce in SQL and Tcl Commands in SQL

Syntax

The syntax to create temporary tables is as follows-

Using the select into statement-

In this method of creating temporary tables, we use the select into a statement, which filters out a part of the usual table's data and stores it in a temporary table. The temporary table's name must start with # in the below syntax.

SELECT

select_list

INTO

temporary_table

FROM

table_name

....

Example-

SELECT

product_name,

list_price

INTO #items --- temporary table

FROM

product

WHERE

brand_id = 10;

Create a temporary table using the create table statement-

The other syntax create a temporary table and then either insert data into it or fetch from some other table

CREATE TABLE #products (

item VARCHAR(MAX),

price DEC(10,2)

);

Lifecycle of temporary table

Generally, the life cycle of the temporary tables is limited to the client sessions, and they are dissolved after the session gets over. In the case of a PHP script, the temporary tables are destroyed immediately after the execution of the script.

Global temporary tables

But, we can also create global temporary tables which can be accessed through all the open connections to the database. We use ## before the name of the global temporary table.

syntax

The syntax for declaring a global temporary table is as follows -

CREATE TABLE ##heller_products (

product_name VARCHAR(MAX),

list_price DEC(10,2)

);

Deletion of temporary tables

The temporary tables can either be deleted manually or automatically deleted when the client session with the database gets terminated. Let's have a look at each of them one by one-

Automatic deletion

The temporary tables created by a connection to the database are automatically deleted if the connection is terminated or the query window is closed without saving the changes. The global temporary tables are deleted only when the connections' queries are completed.

Manual deletion

We can manually delete temporary tables just like usual tables. We use DROP SQL commands, but this has to be done by the connection that has created the temporary table.

syntax

DROP TABLE ##table_name;

We are done with the various concepts related to the temporary tables, so let's move on to the FAQs about the topic.

Frequently Asked Questions

What are temporary tables in SQL?
Temporary tables are a copy of a portion of a larger table to save us time when we need a portion of the table too frequently.
What is the scope of a temporary table?
Temporary tables are accessible during the session in which they were created, and they get automatically destroyed after the session is closed, or they can be deleted manually. But, we can use global temporary tables which are accessible actress different connections.
What are how you can create temporary tables?
We can create temporary tables using two methods-
Using select into a statement where directly fetch data from a previously existing table and store it into a temporary table.
Or we can first create a temporary table and then copy data into it from other tables.
What are global temporary tables?
Global temporary tables are the ones that have the scope beyond the connection that creates them so that various connections can use them. and its lifecycle is not dependent on anyone's connection, but it remains active until the last connection using it is terminated
How we can delete temporary tables?
The temporary tables are automatically deleted when the tables' connection is terminated. But we can also delete them manually.

Conclusion

In this blog, we learned about temporary tables. We started with a brief introduction and then learned the various ways to create them. After that, we moved to learn the different types of temporary tables, viz. Local temporary tables and global temporary tables. Towards the end, we learned how we could delete a temporary table.