Temporary tables in SQL Server

Temporary tables are like normal tables , but these are used to store the result temporarily.We can perform the same operations on the temporary tables as normal tables such as select,delete,update.Unlike normal tables temporary tables are automatically deleted when the user session ends or we manually destroy the table.These tables are stored in the tempdb database.

In SQL Server there are two types of temporary tables

Local Temporary tables

The name of local temporary table start with the pound(#) sign.These are automatically destroyed when the session or the connection which created the table is closed.

Global Temporary tables

The name of global temporary table start with the double pound (##) sign.Unlike local temporary tables once a global temporary table has been created it is destroyed only when the last connection is closed.

In the following example there are two tables defined in the database Users table and UserInfo table.UserInfo table contains the personal details about the user while the Users table contains the account information about the user.

UserInfo table

User table

If we require a temporary result set which contains data from both the tables then we can define a temporary table

CREATE TABLE #UserDetails
(UserName varchar(50),
FirstName varchar(50),
LastName varchar(50),
Email varchar(50))

For populating the temporary table with the  data from both the tables we can use a  INSERT INTO statement as

INSERT INTO #UserDetails
SELECT us.UserName,usi.FIRSTNAME,usi.LASTNAME,us.Email
FROM [dbo].[Users] us
JOIN [dbo].[UserInfo] usi ON us.UserName=usi.UserName

 

Follow on: