Post
Ask Question

Temporary Table in SQL Server

Introduction

In this article, I describe Temporary Tables, the type of Temporary Tables, the 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 the 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 tables 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))

Explorer

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 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

Messages

Removing the duplicate data

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

Output

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.


Similar Articles