SQL Server temporary table usage

Usage:

Temporary tables can be used to store related records for complex queries, which can improve efficiency and program readability, similar
My_cursor declare my_cursor cursor scroll
For select field from tablename
Temporary tables are divided into user temporary tables and system temporary tables.
Differences between the system temporary table and the user temporary table:
1) user temporary table: the name of the user temporary table starts;
The period of the user temporary table exists only in the Session of the user who created the table, and is invisible to other processes.
The temporary table is automatically deleted when the process that created it disappears.
2) system temporary table: the name of the system temporary table starts #
The global temporary table is visible to the entire SQL Server instance, but it is automatically deleted when all sessions accessing it disappear, for example, restarting the database.
Create a temporary table:
1)
Copy codeThe Code is as follows:
Create table TempTableName
(
ID int IDENTITY (1, 1) not null,
A1 varchar (50 ),
A2 varchar (50 ),
A3 varchar (50 ),
Primary key (ID) -- Define ID as a temporary table # primary key of Tmp
)

2) select [Field 1, Field 2,...,] into # Tmp from table


Query temporary table data select * from # Tmp


Delete temporary table drop table # Tmp


Truncate table # Tmp