if exists (SELECT * from tempdb.. sysobjects where id=object_id (' tempdb. #temp '))
drop table #temp
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible in all sessions.
The name of the local temporary table is preceded by a number character (#table_name), and the name of the global temporary table is preceded by two numbered characters (# #table_name).
The SQL statement refers to the temporary table using the name specified for table_name in the CREATE Table statement:
CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT into #MyTempTable VALUES (1)
If a local temporary table is created by a stored procedure or by an application that is executed by multiple users at the same time, SQL Server must be able to distinguish between tables created by different users. To do this, SQL Server appends a numeric suffix internally to the table name of each local temporary table. A temporary table stored in the sysobjects table of the tempdb database, whose full name consists of the table name specified in the CREATE table statement and the system-generated numeric suffix. To allow the suffix to be appended, the table name specified for the local temporary table table_name cannot exceed 116 characters.
Unless you explicitly drop a temporary table by using the DROP table statement, the temporary table is automatically dropped by the system when it exits its scope:
When the stored procedure completes, the local temporary table created in the stored procedure is automatically dropped. This table can be referenced by all nested stored procedures that are executed by the stored procedure that created the table. However, the process that invoked the stored procedure that created this table cannot reference this table.
All other local temporary tables are automatically dropped at the end of the current session.
Global temporary tables are automatically dropped when the session that created this table ends and other tasks stop referencing them. The association between a task and a table is maintained only within the life cycle of a single Transact-SQL statement. In other words, when the session that created the global temporary table ends, the last Transact-SQL statement referencing the table completes, and the table is automatically dropped.
A local temporary table created in a stored procedure or trigger differs from a temporary table of the same name created before a stored procedure or trigger is invoked. If a query references a temporary table, and there are two temporary tables with the same name, you do not define which table to resolve the query against. A nested stored procedure can also create a temporary table with the same name as a temporary table created by the stored procedure that invoked it. All references to table names in nested stored procedures are interpreted as tables created for the nested procedure, such as:
Copy Code code as follows:
CREATE PROCEDURE Test2
As
CREATE TABLE #t (x INT PRIMARY KEY)
INSERT into #t VALUES (2)
SELECT test2col = x from #t
Go
CREATE PROCEDURE Test1
As
CREATE TABLE #t (x INT PRIMARY KEY)
INSERT into #t VALUES (1)
SELECT test1col = x from #t
EXEC Test2
Go
CREATE TABLE #t (x INT PRIMARY KEY)
INSERT into #t VALUES (99)
Go
EXEC Test1
Go
Here is the result set:
(1 row (s) affected)
Test1col
-----------
1
(1 row (s) affected)
Test2col
-----------
2
When you create a local or global temporary table, the CREATE TABLE syntax supports all constraint definitions other than the FOREIGN KEY constraint. If you specify the FOREIGN KEY constraint in a temporary table, the statement returns a warning that the constraint has been ignored and that the table is still created but does not have a FOREIGN key constraint. Temporary tables cannot be referenced in FOREIGN KEY constraints.
Consider using table variables without using temporary tables. Temporary tables are useful when you need to explicitly create an index on a temporary table, or when multiple stored procedures or functions need to use table values. In general, table variables provide more efficient query processing.
