SQL Server Temp Table create query Delete

SQL Server Temp Table create query Delete

CREATE TABLE Temptablename
(
ID int identity (1,1) not NULL,
A1 varchar (50),
A2 varchar (50),
A3 varchar (50),
Primary key (ID)--defines a primary key with an ID of temporary table #tmp
)

Select [Field 1, Field 2,...,] into #tmp from table

Query temporary table Data select * FROM #tmp

Delete temporary table drop table #tmp

Create a global temporary table that is not automatically recycled. Not tested, though seemingly meaningless, what's the difference from a regular table? Might be useful later, so still write down:

Use master;
Go
If object_id (' dbo.sp_globals ') is not null
  drop proc Dbo.sp_globals
Go
Create proc Dbo.sp_globals
As
CREATE TABLE # #globals
(
ID varchar (+) NOT NULL primary key
Value varchar (500)
)
Go
↑SP Content

exec dbo.sp_procoption ' dbo.sp_globals ', ' Startup ', ' true ';

Only show Delete # #globals才会被清除

Indicate
The names of local temporary tables start with a single number sign (#), they are visible only to the current user connection, and are deleted when the user disconnects from the SQL Server instance. The name of the global temporary table begins with a two digit sign (# #), which is visible to any user after it is created, and is removed when all users referencing the table are disconnected from SQL Server.

Table variables are equivalent to ADO's Recordset, much faster than temporary tables.
Table variables cannot be used in the following statements:

INSERT INTO table_variable exec stored procedure.
Select select_list into table_variable statement.

The table variable is automatically cleared when the function, stored procedure, or batch that defines the table variable is finished.
But temporary table support.
. Table variables are much faster than temporary tables (if memory is sufficient)
If the amount of data is small: