Use of SQL Server temporary tables

Use of SQL Server temporary tables

DROP table #Tmp--Delete temporary table #tmp CREATE TABLE #Tmp--Creating a temporary table #tmp ( ID int IDENTITY (FIRST) NOT null--creates the column ID and adds 1 each time a new record is added Wokno varchar (50), Primary key (ID)--defines the primary key for the #tmp of the temporary table ID ); Select * FROM #Tmp--query data for temporary tables TRUNCATE TABLE #Tmp-Clears all data and constraints for the staging table Related examples: Declare @Wokno Varchar (500)--used to record employee numbers Declare @Str NVarchar (4000)--For storing query statements Declare @Count INT--Find out the total number of records Declare @i int Set @i = 0 Select @Count = Count (Distinct (Wokno)) from #Tmp While @i < @Count Begin Set @Str = ' Select top 1 @Wokno = Wokno from #Tmp Where ID not in (Select top ' + Str (@i) + ' ID from #Tmp) ' Exec sp_executesql @Str, N ' @WokNo Varchar output ', @WokNo output Select @WokNo, @i--a line to show the employee number Set @i = @i + 1 End Temp table 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 number characters (# #table_name). The SQL statement references the staging table using the name specified in the CREATE TABLE statement for table_name: 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 concurrently by multiple users, SQL Server must be able to differentiate between tables created by different users. To do this, SQL Server internally appends a numeric suffix to the table name for each local temporary table. The temporary table stored in the sysobjects table in 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 appending suffixes, the table name specified for the local temporary table table_name cannot exceed 116 characters. The temporary table will be automatically dropped by the system when exiting its scope, unless the temporary table is explicitly dropped using the DROP table statement: When the stored procedure finishes, 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 called 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 the table ends and other tasks stop referencing them. The association between a task and a table is persisted only within the lifetime 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 this table is finished 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 that was created before the stored procedure or trigger was called. If the query references a temporary table, and there are two temporary tables with the same name, the query is not defined for which table to resolve. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the stored procedure that called it. All references to table names in a nested stored procedure are interpreted as tables created for the nested procedure, for example: 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 The CREATE TABLE syntax supports all constraint definitions other than the FOREIGN KEY constraint when creating a local or global temporary table. If you specify the FOREIGN KEY constraint in a temporary table, the statement returns a warning message stating that the constraint has been ignored, that the table will still be created, but does not have a FOREIGN KEY constraint. Temporary tables cannot be referenced in the FOREIGN KEY constraint. Consider using table variables without using temporary tables. Temporal tables are useful when you need to explicitly create an index on a temporary table, or when more than one stored procedure or function needs to use a table value. Typically, table variables provide more efficient query processing. Transferred from: http://www.cnblogs.com/shineqiujuan/archive/2008/11/13/1332657.html

Use of SQL Server temporary tables