SQL Server - how to create temporary table - Asked By mani on 21-Feb-11 09:30 AM

hi..i am using c# with asp.net...
i have one main table to store all data..but i have to create temperory table to store date after that i have to move and update with data already stored in main table.and it has to destroy data...dynamically....any body help me..
Jatin Prajapati replied to mani on 21-Feb-11 09:32 AM
Hi Mani,
Please see below sql for creating temporary table.

CREATE TABLE #Customer
(
CustomerID int identity(1,1),
CustomerName varchar(100),
CompanyName varchar(100),
Address varchar(250)
)
Reena Jain replied to mani on 21-Feb-11 09:37 AM
hi,

yes you can create a temporary table in sql server like this
CREATE TABLE #employee (
ID int,
Name char(30) )
You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table

Hope this will help you
Reena Jain replied to mani on 21-Feb-11 09:38 AM
hi,

Creating a temporary table is virtually the same as creating a normal table. The main exception is the naming of the table. A hash ('#') character as the first character in the table name denotes that it is a temporary table.

There are two types of temporary tables, local and global.

A local temporary table has a single hash ('#') at the start of its name. A local temporary table is visible only to the user who created it and is destroyed automatically when that user disconnects.

A global temporary table is denoted by a name starting with two hashes (i.e. '##'). A global temporary table is visible to all users and is deleted automatically when the last user who has referenced the table disconnects.

An example of creating a local temporary table:

create table #foo
(
    CarIndex  smallint,
    CarType   varchar(20)
)


hope this will help you
Daivagna Nanavati replied to mani on 23-Feb-11 01:43 PM
Hi Mani

There are two type of temporary tables in sql, one is which is accessible till the last connection to that connection request is made if not destroyed, and one is which is accessible only for current connection request, than after they would get distroyed, you can create table using CREATE TABLE #temp  but the thing is these are the tables you need to destroy them by droping them in code but there are one type of table which fits your requirement as you said "destroy data dynamically", so you can do like following

DECLARE TABLE @Temp(Id INT,Name VARCHAR(100))

this is the table which will get destroyed as soon as the connection is closed, you will not have to drop it manually..

let me know

THanks