Summary of SQL temporary table usage _mssql

1, create the method:

Method One:

CREATE TABLE Temptablename
Or
Select [Field 1, Field 2,...,] into temptablename from table

Method Two:

CREATE table tempdb. mytemptable (Tid int)

Description
(1), the temporary table is actually placed in the database tempdb in a user table;
(2), temptablename must take "#", "#" can be one or two, to # (local) or # # (Global) table, which exists during the session, the end of the session is automatically deleted;
(3), if the creation is not by # or # #开头, but with tempdb. TempTable to name it, the table can exist until the database is restarted.

2. Manually delete

drop table Temptablename
Description
The drop TABLE statement explicitly drops the temporary table, otherwise the temporary table is automatically dropped by the system when it exits its scope:
(1), 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 invokes the stored procedure that created this table cannot reference this table;
(2), all other local temporary tables are automatically dropped at the end of the current session;
(3), the global temporary table is automatically dropped when the session that created the table ends and other tasks stop referencing it. 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.

3. Sample Code

(1) Create

Use TestDB-
creates a local temporary table create 
table #tmpStudent (Tid int,name varchar, age int)
inserts into #tmpStudent VALUES (' Xiaowang ',)
select * FROM #tmpStudent
--Create a local temporary table another way to type
select * into #tmpStudent from student
SELECT * FROM #tmpStudent

The second method of creation:

CREATE table tempdb. mytemptable (Tid int)--with corresponding permissions to write

(2) Delete

drop table #tmpStudent

About the life cycle of temporary tables in SQL

1. A local temporary table (#开头) is only valid for the current connection and is automatically deleted when the current connection is disconnected.
2. The Global temporary table (# #开头) is also valid for other connections and is automatically deleted when the current connection and other connections that have access to it are disconnected.
3. Either a local temporary table or a global temporary table, you can explicitly delete a drop table #Tmp (or drop table # #Tmp) As long as the connection has access rights.