temp table in sql

Introduction:

Here I will explain how to use temp table in SQL. Sometimes required to create and save temporary data for some calculation in SQL server that time use to temp table in SQL.

So, Now I explaining how to use temporary table in SQL. 


Description: 

In previously post I explained to  
Three tier architecture in asp.net
Remove .aspx from url in asp.net
file type validation in jquery
Change the column name or datatype or size in sql server
How to set or get textbox value using Javascript or Jquery
Calling web service without adding web reference  






And now here I explain one by one operation and use of temp table in SQL.

Declare temp table in SQL
Insert record in temp table
Insert record from existing table in temp table
insert into temp table in SQLselect record from temp table
Fetch one by one record from temp able
Delete record from temp table 

temporary tables in sql


Example:

Declare temp table in SQL

DECLARE @tempTable TABLE (

                                  ID INT,

                                  FirstName Varchar(250),

                                  LastName Varchar(250)

                                  )

 

Insert record in temp table

INSERT INTO @tempTable  Values (101,‘Dhruvik’,’NK’)

Insert record from existing table in temp table

       INSERT INTO @tempTable

                     SELECT top 10 ID, FirstName,LastName

                                  FROM tblUserDetails

 

select record from temp table

SELECT * FROM @tempTable

Fetch one by one record from temp able

WHILE EXISTS (SELECT 1 FROM @tempTable)

       BEGIN

              SELECT TOP 1 @ID = ID,

                                  @FirstName = FirstName,

                                  @LastName = LastName

              FROM @tempTable

              DELETE FROM @tempTable WHERE ID=@ID

      

       END

 

Delete record from temp table 

DELETE FROM @tempTable WHERE ID=101

It means we can execute same query as we executing on physically table (like CREATE TABLE ).