Temporary table usage

InSQL Server2000.3Type:

1)Create Table # table_name (field1 type, field2 type ,..........)

Insert into # table_name values (..............)

Select * from # table_name

2)Create Table tempdb. table_name (field1 type, field2 type ,..........)

Insert into # table_name values (..............)

Select * from # table_name

3) Select * into # temp from (select * From func)

Select * from # temp

Drop table # temp

Note:

(1) A temporary table is created by the creator. Multiple users can run this statement at the same time without worrying about duplicate table names. When the database is exited or the transaction is committed, the table is automatically deleted. The created table needs to be manually deleted, which is different from the normal table: The table is placed in a temporary space of the database. If the table is not manually deleted, when the database restarts, the database management system will automatically delete it.

(2) There are two types of temporary tables:Local and global. They differ in terms of name, visibility, and availability. The name of the local temporary table starts with a single digit (#). They are only visible to the current user connection and are deleted when the user is disconnected from the SQL server instance. The name of the global temporary table starts with two numeric symbols (#). After being created, the table is visible to all users. When all users that reference the table are disconnected from SQL Server, the table is deleted.

(3) There is another temporary table creation method:

Declare @ table_name table (field1 type, field2 type ,..........)

When a temporary table is released at the end of a statement, several tables with the same name can be created in a session, but several temporary tables with the same name cannot be declared in the same statement. The usage is as follows:

Set quoted_identifier on
Go
Set ansi_nulls on
Go
Create proc spgettreevar (@ parentid INT)
As
Begin
Set nocount on
/* If not Sqlserver2000 Temporary tables can be used. */
Declare @ tmp1 table (parentid int, Id int, isclass INT)
Declare @ tmp2 table (parentid int, Id int, isclass INT)
Declare @ tmp3 table (parentid int, Id int, isclass INT)

Insert @ tmp1 select parentid, ID, iscls from variables where parentid = @ parentid and isdelete = 0
Insert @ tmp3 select parentid, ID, iscls from variables where parentid = @ parentid and isdelete = 0

/* The number of cycles equals to the depth of the tree. */
While exists (select * From @ tmp1 where isclass = 1)
Begin
Insert @ tmp2 select a. parentid, A. ID, A. iscls from variables A, @ tmp1 B where a. parentid = B. ID and isdelete = 0
/* @ Tmp2 All nodes at the level of the current query are saved in the table. */
Delete from @ tmp1 where isclass = 1
/* @ Tmp1 The table ultimately stores leaf nodes. */
Insert @ tmp1 select * From @ tmp2
/* @ Tmp3 The table stores the children that are retrieved each time. */
Insert @ tmp3 select * From @ tmp2
Delete from @ tmp2
End
Select distinct variables. * from variables inner join @ tmp1 B on variables. ID = B. ID
Set nocount off
End

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go

Determine whether the database has been saved

Judge a common table:

If exists (select * From DBO. sysobjects where id = object_id (n'Table Name') And objectproperty (ID, n' isusertable') = 1)

Print 'exists'

Or

If (object_id ('Table Name') Is not null)

Print 'exists'

 

Judge temporary table:

If object_id ('tempdb ..Table Name') Is not null

Print 'exists'

Or:If object_id ('Table Name') Is not null

Print 'exists'