First, we will introduce the classification of temporary tables: There are two types of temporary tables
1. Add # Only visible to this session, and the SQL server automatically deletes the session after it ends.
2. Add ## global. After all sessions, SQL Server deletes them.
Global temporary table # tablename can be accessed by any process.
To use a temporary table in ASP, you must use a global temporary table to change the local temporary table in your stored procedure to the global one. However, temporary tables do have short lifecycles.
Special temporary tables (cursors) can also be inserted directly.
Specify the query result to temporary table B
Select * from a into cursor B
Use select to directly create the table structure, omitting the create process select * into # tbl_template from employees
Select * from # tbl_template
Drop table # tbl_template
Put the result set of a stored procedure into a temporary table in two ways:
1. Create a table before executing the stored procedure:
Create Table # T (...)
Insert into # T exec B
Select * from # T
Drop table # T
2. Directly select into temporary table: if a temporary table is used in the stored procedure, use set fmtonly off.
Select * into # T from OpenRowSet (
'Sqlodb', 'server = servername; uid = sa; Pwd = 123; database = testdb ',
'Set fmtonly off; Set nocount on; Exec B ') as
Select * from # T
Drop table # T