Temporary Table in SQL Server

Introduction

In this article I describe Temporary Tables, type of Temporary Tables, creation of Temporary Tables and the uses of Temporary Tables.

Temporary Tables

Temporary Tables provide short term use of data. Temporary Tables are created at run time and provide all the same functionality as a general table. Temporary Tables are created using # preceding the name of table. These tables are stored in a Tempdb database.

Types of Temporary Tables

1. Local Temporary Table
2. Global Temporary Table

Local Temporary Table

Local Temporary Tables are created using a single # preceding the table name. They are available for the current connection of the current user, once the user exits from the current session these table are dropped.

Example of Local Temporary Table:

create table #employee(empId int)
 
Global Temporary Table

Global Temporary Tables are created using ## preceding the table name. Once these tables are created, they are available for all the connections, the same as a simple table. It is dropped when all the connections are closed.

Example of Global Temporary Table:

create table ##emppp(empName varchar(15))



Use of Temporary Tables

There are several uses of Temporary Tables; some are the following:

1. Keep the result of a called Stored Procedure
2. Reduce the number of rows for joins
3. Aggregate data from various sources
4. Replaces cursors and for parameterized views

Removing the duplicate data from the table using a Temporary Table and Distinct keyword

First of all we create a table in which we insert repeated data, then by the use of a Temporary Table and the distinct keyword we remove the duplicate data.

Creation of table:

create table emp(empid int, empName varchar(15), empSal varchar(15))

Insertion of data:
 

insert into emp

select 1,'d','50000'union all

select 1,'d','50000'union all

select 2,'e','55000'union all

select 2,'e','55000'union all

select 3,'f','65000'union all

select 3,'f','65000'union all

select 4,'g','75000'union all

select 4,'g','75000'

Output:
 

select * from emp
 

Removing the duplicate data:
 

select * into #empp from emp drop table emp select Distinct * into emp from #empp

Output:



We can use a general table instead of a Temporary Table but then we have to drop the table when we are finished with it.

Summary

In this article I described Temporary Tables in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.