SQL Server Temp Table

Use of temporary tables
What is a temp table

Temporary tables are divided into two types: 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 global temporary table has a name preceded by two number characters ##table_name .

usage scenarios for temporary tablesData two times filter

After the table is created in the database, for complex queries, you can introduce temporary tables, insert data into the staging table, and then query the data from the staging table.

caching data at the database level

Build frequently used data, insert it into a temporary table, and use it as a cache.

Grammar

Create a temporary table

Method One:

CREATE Table #临时表名 (Field 1 constraint,                      field 2 constraint,                  ...) CREATE TABLE # #临时表名 (field 1 constraint,                          field 2 constraint,                      ...)

Method Two:

SELECT *into # #临时表名 from your table;

Note: The above # represents a local temporary table, # #代表全局临时表

Querying temporary tables

SELECT ** from # #临时表名;

Delete temporary table

Drop Table #临时表名; Drop table # #临时表名;

Example

Used in SQL statements:

Drop Table#Tmp--Delete temporary table #tmpCreate Table#Tmp--Create a temporary table #tmp(IDint IDENTITY(1,1) not NULL,--Create a column ID, and add 1 each time you add a recordWoknovarchar( -),       Primary Key(ID)--defines the primary key for the #tmp of the temporary table ID);Select *  from#Tmp--querying data for temporary tablestruncate Table#Tmp--emptying all data and constraints on the staging table

Used in stored procedures:

Declare @Wokno Varchar (500)--Used to record employee number Declare @Str NVarchar (4000)--to hold the query statement Declare @Count INT--To find the total number of records      Declare @i int Set @i = 0 Select @Count = Count (Distinct (Wokno)) from #TmpWhile @i < @Count     Begin       Set @Str = ' Select top 1 @Wo Kno = Wokno from #Tmp Where ID not in (Select top ' + Str (@i) + ' ID from #Tmp) '       Exec sp_executesql @Str, N ' @WokNo varch AR (+) output ', @WokNo output       Select @WokNo, @i--a line to display the employee number       Set @i = @i + 1End

principle

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.

Life cycle

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 PROCEDURETest2 asCREATE TABLE#t (xINT PRIMARY KEY)INSERT  into#tVALUES(2)SELECTTest2col=X from#tGOCREATE PROCEDURETest1 asCREATE TABLE#t (xINT PRIMARY KEY)INSERT  into#tVALUES(1)SELECTTest1col=X from#tEXECTest2GOCREATE TABLE#t (xINT PRIMARY KEY)INSERT  into#tVALUES( About)GOEXECTest1GO

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 FOREIGN KEY all constraint definitions except constraints. If you specify a constraint in a temporary table FOREIGN KEY , the statement returns a warning message stating that the constraint has been ignored and that the table is still created, but does not have a FOREIGN KEY constraint. FOREIGN KEYtemporary tables cannot be referenced in constraints.

Summarytable variables and temporary tables

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.

SQL Server Temp Table