SQL temporary table usage summary, SQL table usage Summary

SQL temporary table usage summary, SQL table usage Summary

1. Creation method:

Method 1:

Create table TempTableName
Or
Select [Field 1, Field 2,...,] into TempTableName from table

Method 2:

Create table tempdb. MyTempTable (Tid int)

Note:
(1) temporary tables are actually a user table in the database tempdb;
(2) TempTableName must contain "#" and "#" can be one or two tables starting with # (local) or # (global, this table exists during the session and is automatically deleted after the session ends;
(3) If you do not start with # Or # when creating the table, but use tempdb. TempTable to name it, the table can exist until the database is restarted.

2. manually delete

Drop table TempTableName
Note:
The drop table statement explicitly removes the temporary TABLE. Otherwise, the temporary TABLE will be automatically removed by the system when it exits its scope:
(1) When the stored procedure is complete, the local temporary table created in the stored procedure is automatically removed. All nested stored procedures executed by the stored procedure of the created table can reference this table. However, the process that calls the stored procedure to create this table cannot reference this table;
(2) All other local temporary tables are automatically removed at the end of the current session;
(3) The global temporary table is automatically removed when the session for this table is created and other tasks are stopped. The association between tasks and tables is only maintained during the lifecycle of a single Transact-SQL statement. In other words, when the session for creating a global temporary table ends, the table is automatically removed after the last Transact-SQL statement that references the table is completed.

3. Sample Code

(1) Create

Use testdb -- create table # tmpStudent (Tid int, Name varchar (50), Age int) insert into # tmpStudent values ('xiaowang ', 25) select * from # tmpStudent -- create a local temporary table. select * into # tmpStudent from studentselect * from # tmpStudent

Method 2:

Create table tempdb. MyTempTable (Tid int) -- you have the permission to write this statement.

(2) Delete

Drop table # tmpStudent

Lifecycle of temporary tables in SQL

1. The local temporary table (starting with #) is only valid for the current connection and is automatically deleted when the current connection is disconnected.
2. The global temporary table (starting with #) is also valid for other connections. It is automatically deleted when the current connection and other connections that have accessed it are disconnected.
3. You can use drop table # Tmp (or drop table # Tmp) to explicitly delete a local temporary table or global temporary table as long as the connection has access permissions.