Use of temporary tables for SQL Learning Series (ii)

1. Notes on the use of temporary tables:

(1). The temporary table is actually a user table placed in the database tempdb.

(2). Temptablename must have "#" and "#" can be one or two tables that start with # (local) or # # (global), which exists during the session and is automatically deleted when the session ends.

(3). If you do not #开头 with # or # when you create it, use tempdb instead. TempTable to name it, the table can persist until the database is restarted.

2. Delete temporary tables

drop table Temptablename

(1). 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.
(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 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.

3. Using the Code

--Delete temporary table drop table #Student--creates a temporary table #Student the CREATE TABLE (name nvarchar ( -) notNULL, Ageint)--EnquirySelect* from#Student--add insert into #Student values ('Zhang San', -)--Remove Delete #StudentwhereName='Zhang San'--Modify the update #StudentSetAge= - whereName='Zhang San'

View Code

Use of temporary tables for SQL Learning Series (ii)