SQL Server creates temporary tables

Create a temporary table ?????? Method 1 :???? Createtable # temporary table name (Field 1 constraint ,?????????????????????? Field 2 constraints ,??????????????????.....)???????? Createtable # temporary table name (Field 1 constraint ,?????????????????????????? Field 2 constraints ,?????????

Create a temporary table ?????? Method 1 :???? Create table # temporary table name (Field 1 constraint ,?????????????????????? Field 2 constraints ,??????????????????.....) ???????? Create table # temporary table name (Field 1 constraint ,?????????????????????????? Field 2 constraints ,?????????

CreateTemporaryTable
?????? Method 1:
???? Create table #TemporaryTable Name (Field 1 constraints,
?????????????????????? Field 2 constraints,
??????????????????.....)
???????? Create table ##TemporaryTable Name (Field 1 constraints,
?????????????????????????? Field 2 constraints,
??????????????????????.....)
???????? Method 2:
???? Select * #TemporaryTable name from your table;
?????? Select * ##TemporaryTable name from your table;
Note: The above # indicates the localTemporaryTable, # indicates the globalTemporaryTable

QueryTemporaryTable
???? Select * from #TemporaryTable Name;
?????? Select * from ##TemporaryTable Name;

DeleteTemporaryTable
???? Drop table #TemporaryTable Name;
?????? Drop table ##TemporaryTable Name;

?

?

?

SQL SERVERTemporaryTable usage

Drop table # Tmp ?? -- DeleteTemporaryTable # Tmp
Create table # Tmp --CreateTemporaryTable # Tmp
(
??? ID ?? Int IDENTITY (1, 1 )???? Not null ,--CreateColumn ID. Each time a new record is added, 1 is added.
??? WokNo ??????????????? Varchar (50 ),??
??? Primary key (ID )????? -- Define IDTemporaryTable # primary key of Tmp ?????
);
Select * from # Tmp ??? -- QueryTemporaryTable Data
Truncate table # Tmp -- clearTemporaryAll table data and constraints

Example:

Declare @ Wokno Varchar (500) -- used to record employee numbers
Declare @ Str NVarchar (4000) -- used to store query statements
Declare @ Count int -- obtain 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 (500) output', @ WokNo OutPut
?????? Select @ WokNo, @ I -- display the employee ID in one row
?????? Set @ I = @ I + 1
??? End

TemporaryTable
YesCreateLocal and globalTemporaryTable. LocalTemporaryThe table is only visible in the current session; GlobalTemporaryThe table is visible in all sessions.
LocalTemporaryThe table name is preceded by a number character (# table_name), while the globalTemporaryThe table name is preceded by two numbering characters (# table_name ).

The SQL statement uses the CREATE TABLE statement to reference the name specified for table_name.TemporaryTable:

Create table # MyTempTable (cola int primary key)
Insert into # MyTempTable VALUES (1)

IfTemporaryTable by stored procedureCreateOr applications executed by multiple users at the same timeCreateSQL Server must be able to distinguish between different usersCreate. Therefore, SQL Server internallyTemporaryAppend a numeric suffix to the table name. Stored in the sysobjects table of the tempdb DatabaseTemporaryThe full name of a TABLE is composed of the TABLE name specified in the create table statement and the digital suffix generated by the system. To allow append suffixesTemporaryThe table name specified by the table cannot exceed 116 characters.

Unless explicitly removed using the drop table statementTemporaryTable. OtherwiseTemporaryThe table will be automatically removed by the system when it exits its scope:

When the stored procedure is completed, it is automatically removed from the stored procedure.CreateLocalTemporaryTable. ByCreateThis table can be referenced by all nested stored procedures executed by the table's stored procedure. HoweverCreateThe stored procedure process of this table cannot reference this table.

All other localTemporaryThe table is automatically removed at the end of the current session.

GlobalTemporaryTable inCreateThis table is automatically removed when the session ends and other tasks stop referencing it. The association between tasks and tables is only maintained during the lifecycle of a single Transact-SQL statement. In other words, whenCreateGlobalTemporaryWhen the table session ends, the last Transact-SQL statement that references the table is deleted automatically.
In a stored procedure or triggerCreateLocalTemporaryTable and before calling a stored procedure or triggerCreateThe same nameTemporaryDifferent tables. If the query referencesTemporaryTable, and two tables with the same nameTemporaryThe table does not define which table to parse the query. Nested stored procedures can alsoCreateAnd the stored procedure that calls itCreateOfTemporaryTable with the same nameTemporaryTable. All references to table names in nested stored procedures are interpretedCreateFor 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

The following is the result set:

(1 row (s) affected)

Test1Col ???
-----------
1 ??????????

(1 row (s) affected)

Test2Col ???
-----------
2 ??????????

WhenCreateLocal or globalTemporaryThe create table syntax supports all the constraint definitions except the foreign key constraint. IfTemporaryIf the foreign key constraint is specified in the table, the statement returns a warning message indicating that the constraint has been ignored and the table will stillCreateBut does not have the foreign key constraint. Cannot be referenced in the foreign key constraintTemporaryTable.

Consider using Table variables instead of usingTemporaryTable. When you needTemporaryTable explicitlyCreateWhen indexes or multiple stored procedures or functions need to use table values,TemporaryTable is useful. Generally, table variables provide more effective query processing.