Temporary Table

How To Use Temporary Tables In SQL

Introduction:
In this article, you will learn what Temporary Tables are and how to use Temporary Tables in SQL.

SQL Temporary tables are used temporarily in the database. It creates for a particular session or a time. These tables are stored in temp db. These tables create at the execution time and we can use these tables as a normal table. The code Scope of these tables is limited. They work like a simple table in SQL.

SQL Temporary tables have two types -

• Local Temporary table

• Global Temporary table

Local Temporary Table

A Local Temporary table is created for a particular time duration. We can use all the syntax (Insert, Select, Create) in the temporary table, which is similar to the normal table in the database. It is defined with the “#” symbol before the table name.

Syntax:

The basic syntax to create Local Temporary Table is as follows:

create table #table_name(

field_name int,

field_name varchar(),

field_name int

)

Step 1: We write the query to create Temporary Table.

Step 2: Here, our Temporary table is created. We can see this table in our database.

Go to SQL Server -> Expand Databases->System Databases->tempdb->Temporary Tables.

Step 3: Now, insert some data in the Temporary tables.

insert into #Temorary_Table (Id, Name, Age) values (1,'Avi', 32);

insert into #Temorary_Table (Id, Name, Age) values (2,'Ashu', 52);

insert into #Temorary_Table (Id, Name, Age) values (3,'Guddu', 55);

insert into #Temorary_Table (Id, Name, Age) values (4,'Astha', 45);

Step 4: To show the result, first write the Select query.

Step 5: The output is:

Global Temporary table

A Global Temporary table is also created for a particular time duration. Global temporary tables are seen by all the users. A Global Temporary table syntax is same as the normal table in the database. It is defined with the “##” symbol before the table name.

Syntax:

The basic syntax to create Global Temporary Table is as follows:

create table ##table_name(

field_name int,

field_name varchar(),

field_name int

)

Step 1: We write the query to create Temporary Table.

CREATE TABLE ##Global_table

(

id int,

Name varhar(30) ,

Age int

)

Step 2: We can also insert the data in Global Temporary table, as shown below:

insert into #Temorary_Table (ID, Name, Age) values (2,'Ashu', 52);

insert into #Temorary_Table (ID, Name, Age) values (3,'Guddu', 55);

insert into #Temorary_Table (ID, Name, Age) values (4,'Astha', 45);

Step 4: To show the result, first write the Select query.

Step 5: The output is:

Summary:
Thus, we learned, that Temporary tables are created in a fixed session or are used to store the temporary data in SQL.