Tuesday, 29 July 2014

Create temp table in SQL Server

     SQL Server provides the feature temporary table which helps user to replace Stored Procedure that requires large number of row manipulation and to replace complex SQL Join operations. The temp table can be created at runtime and can do all kind of operations like normal table. But, as its name implies, the scope is limited.

SQL Server provides two types of temp tables based on its connection life time.

Local Temp Table

Local temp tables are only available to the current connection for the user, and are deleted when the connection is closed. it requires the single hash value "#" as the prefix when created.

Syntax: (CREATE TABLE #MyTempTable).
--Create Local Temp Table
CREATE TABLE #UserTempTable(
UserID int, UserName varchar(250), 
Email varchar(250))

--Insert rows into Local Temp Table
Insert into #UserTempTable values(1,'Morgan','Moragn@MorganTechSpace.com')
Insert into #UserTempTable values(2,'Tim','Tim@MorganTechSpace.com')
Insert into #UserTempTable values(2,'Shiv','Shiv@MorganTechSpace.com')

--Select rows from Local Temp Table
Select * from #UserTempTable
How to Create temp table in SQL Server

Global Temp Table

Global Temp tables are visible to all connections of SQLServer, and only destroyed when the last connection referencing the table is closed. it requires the double hash value "##" as the prefix when created.

Syntax: (CREATE TABLE ##MyTempTable).
--Create Global Temp Table
CREATE TABLE ##UserTempTable(
UserID int,
UserName varchar(250), 
Email varchar(250))

--Insert rows into Global Temp Table
Insert into ##UserTempTable values(1,'Morgan','Moragn@MorganTechSpace.com')
Insert into ##UserTempTable values(2,'Tim','Tim@MorganTechSpace.com')
Insert into ##UserTempTable values(2,'Shiv','Shiv@MorganTechSpace.com')

--Select rows from Global Temp Table
Select * from ##UserTempTable
Both Temporary tables are created in tempdb. Whenever we create new temp table, we can see it under Temporary folder of tempdb database.

Create Local temp table in SQL Server

Thanks,
Morgan
Software Developer

Advertisements
Advertisements

No comments:

Post a Comment