RSS

Temporary Tables in MS SQL Server

24 Oct

Temp Tables

These tables can be created at runtime and can do the all kinds of operations that one normal table can do. But, based on the table types, the scope is limited. These tables are created inside tempdb database.

Types of Temporary Tables

  • Local Temp Tables: are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash (“#“) sign.
  • Global Temp Tables: the name of these tables starts with a double hash (“##“). Once this table has been created by a connection, like a permanent table it is then available to any user by any connection. It can only be deleted once all connections have been closed.

How to … ??

Create Local Temp Tables … you can craete the local temp tables that defined above by using CREATE TABLE statement as following:

CREATE TABLE #TableName
(
ID int,
CustomerName varchar(50),
CustomerCity varchar(150)
)

After execution of the previous script you can insert, delete or update data within the table as you do with the normal or the real table.

INSERT INTO #TableName VALUES (1, ‘Ahmed Negm’, ‘EGYPT, Cairo’);
INSERT INTO #TableName VALUES (1, ‘Hana Negm’, ‘@ My Heart’);

—————-

SELECT * FROM #TableName;

Notice please … if you close your query window and open a new one, then try to INSERT or SELECT you will see this error:

Msg 208, Level 16, State 0, Line 1
Invalid object name ‘#TableName’.

Create Global Temp Tables … you can craete the global temp tables that defined above by using CREATE TABLE statement as following:

CREATE TABLE ##GlobalTableName
(
ID int,
CustomerName varchar(50),
CustomerCity varchar(150)
)

—————-

INSERT INTO ##GlobalTableName VALUES (1, ‘Ahmed Negm’, ‘EGYPT, Cairo’);
INSERT INTO ##GlobalTableName VALUES (1, ‘Hana Negm’, ‘@ My Heart’);

—————-

SELECT * FROM ##GlobalTableName;

When can I decide to use ‘Temp Tables‘ ??

    1. When we are doing large number of row manipulation in stored procedures.
    2. This is useful to replace the cursor. We can store the result set data into a temp table, then we can manipulate the data from there.
    3. When we are having a complex JOIN operation.
 
Leave a comment

Posted by on October 24, 2011 in MS SQL Server

 

Tags: , , , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: