How to create temporary tables in sql server 2005-2008:

To create temporary is very easy in sql server .Syntax is given below:

create table TempTable name (columns name  datatype null/not null )

Example

create table #MyTempTable (Srno int not null identity(1,1),student_name varchar(50))

In this way you can create a temporary table in sql server 2005-2008.Now how to insert values into temporary table. In below example we can see how to insert values into temp table # MyTempTable.

insert into #MyTempTable(student_name)

select 'Tom'

union

select 'haword'

Always remember that once you have created a temporary table it is important to delete the table and its records if you want to reuse the same temp table for new operation. If you do not delete the temp table and executes the same queries again it will throw an exception.

Msg 2714, Level 16, State 6, Line 3

There is already an object named '#MyTempTable' in the database.

So always delete the temp table and its records once you are done with the processing of queries.

delete from #MyTempTable

drop table #MyTempTable