BLOGS

Temporary Tables & Table Variable

In SQL Server, temp tables are created at run time. It is just like a normal table and it will support all the operations but these tables are created inside temp database. The scope of the table is only on session & connection.

There are two types of temporary tables,

1. Local Temp Table
2. Global Temp Table

Local Temp Table

It will be available only on SQL Server session or connection. It means, if the session is closed or connection is out then this temp table will not be accessible, it will be deleted.

This will be accessible only one user who creates the table.

Local Temp table name is started with “#” sign.

Syntax

CREATE TABLE #LocalTemp

(

 …Columns

)

Example

–Create a local Temp Table

CREATE TABLE #LocalTemp

(

 UserID int,

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

)

GO

–Insert values directly

Insert into #LocalTemp values (1,‘Hari’, ‘Test@Test.com’, ‘123-245-356’)

Go

–Insert value from another table

Insert into #LocalTemp 
(UserID, Name, EmailId, Phonenumber)

(SELECT UserId, Name, EmailId, Phonenumber from dbo.USERS)

Go

Try to open a new query window and access the above table then see the result by yourself.

Global Temp Table

This table will be available for all SQL Server sessions. This can be accessible by multiple users with the same connections. It will be deleted when a connection is closed or all the sessions are closed.

Global Temp table name is started with “##” sign

Syntax

CREATE TABLE ##GlobalTemp

(

 …Columns

)

GO

Example

–Create a local Temp Table

CREATE TABLE ##GlobalTemp

(

 UserID int,

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

)

GO

–Insert values directly

Insert into ##GlobalTemp values (1,‘Hari’, ‘Test@Test.com’, ‘123-245-356’)

Go

–Insert value from another table

Insert into ##GlobalTemp 
(UserID, Name, EmailId, Phonenumber)

(SELECT UserId, Name, EmailId, Phonenumber from dbo.USERS)

Go

Open multiple query window and access this table then close the connection and connect again. Then try to access the table by yourself.

Table Variable

Apart from temp tables, we have table variable and the scope of this table variable only with the batch of script.

It will be dropped once it comes out of the batch.

This is also created on temp database but not on memory. This allows us to create primary key on table as like normal table when declare a table variable but the difference is we cannot create cluster index.

Example

–First Batch Start

–Create a table variable

DECLARE @Users TABLE

 (

 UserID int
identity (1,1),

 Name varchar(50),

 EmailId varchar(50),

 Phonenumber varchar(15)

 )

  –insert values from another table

 Insert into @Users
(Name, EmailId, Phonenumber)

 SELECT  Name, EmailId, Phonenumber from dbo.USERS

 –Select the records

 SELECT * FROM @Users

 –First Batch End …………..

 –Second batch start

 SELECT * FROM @Users –gives error

 Go

 –Second Batch End….