SQL Server Temp Table operations

A temporary table is a table that is built into a temporary system folder and, if used properly, can be done in a variety of ways, like a normal table, and automatically released when VFP (Visual FoxPro, the latest Visual database management system platform introduced by Microsoft) exits.

You can create local and global temporary tables. Local temporary tables are visible only in the current session, and global temporary tables are visible in all sessions. The name of the local temporary table is preceded by a number sign (#), and the name of the global temporary table is preceded by two number characters (# #).

If a local temporary table is created by a stored procedure or by an application that is executed concurrently by multiple users, SQL Server must be able to differentiate between tables created by different users. To do this, SQL Server internally appends a numeric suffix to the table name for each local temporary table. The temporary table stored in the sysobjects table in the tempdb database, whose full name consists of the table name specified in the Create table statement and the system-generated numeric suffix. To allow appending suffixes, the table name specified for the local temporary table table_name cannot exceed 116 characters.

The temporary table will be automatically deleted by the system when exiting its scope, unless the temporary table is explicitly dropped by using the Drop table statement.

When the stored procedure finishes, 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 called the stored procedure that created this table cannot reference this table.

All other local temporary tables are automatically deleted at the end of the current session.

Global temporary tables are automatically dropped when the session that created the table ends and other tasks stop referencing them. The association between a task and a table is persisted only within the lifetime 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 this table is finished and the table is automatically dropped.
A local temporary table created in a stored procedure or trigger differs from a temporary table of the same name that was created before the stored procedure or trigger was called. If the query references a temporary table, and there are two temporary tables with the same name, the query is not defined for which table to resolve. A nested stored procedure can also create a temporary table with the same name as the temporary table created by the stored procedure that called it. All references to table names in a nested stored procedure are interpreted as tables created for the nested procedure.

The CREATE TABLE syntax supports all constraint definitions other than the Foreign Key constraint when creating a local or global temporary table. If you specify the Foreign Key constraint in a temporary table, the statement returns a warning message stating that the constraint has been ignored, that the table will still be created, but does not have a Foreign Key constraint. Temporary tables cannot be referenced in the FOREIGN KEY constraint.

Consider using table variables without using temporary tables. Temporal tables are useful when you need to explicitly create an index on a temporary table, or when more than one stored procedure or function needs to use a table value. Typically, table variables provide more efficient query processing.

First, create a temporary table
method One: Create directly
CREATE TABLE #临时表名

(Field 1 Constraints,
Field 2 Constraints,
.....)
CREATE TABLE # #临时表名

(Field 1 Constraints,
Field 2 Constraints,
.....)
method Two: Insert a specific data creation
SELECT * into #临时表名 the from table name;
SELECT * Into # #临时表名 the from table name;

Ii. inserting data into temporary tables

Create Table #临时表名 (number int Primary Key)
Insert into #临时表名 Values (1)

Second, the Query temporary table
SELECT * from #临时表名;
SELECT * from # #临时表名;

Third, delete temporary table
drop table #临时表名;
drop table # #临时表名;

Iv. emptying all data and constraints on temporary tables

TRUNCATE TABLE #临时表名;
TRUNCATE TABLE # #临时表名;

SQL Server temporary table actions