Creating Temporary Tables

No special permissions are required to create temporary tables or to execute commands that may require storage space in the temporary database.

Create temporary tables either by preceding the table name in a create table statement with a pound sign (#), or by specifying the name prefix “tempdb..”.

Temporary tables created with a pound sign are accessible only by the current SAP ASE session: users on other sessions cannot access them. These nonsharable, temporary tables are destroyed at the end of each session. The first 13 bytes of the table’s name, including the pound sign (#), must be unique. SAP ASE assigns the names of such tables a 17-byte number suffix. (You can see the suffix by querying tempdb..sysobjects.)

Temporary tables created with the “tempdb..” prefix are stored in tempdb and can be shared among SAP ASE sessions. SAP ASE does not change the names of temporary tables created this way. The table exists either until you restart SAP ASE or until its owner drops it using drop table.

System procedures work on temporary tables, but only if you use them from tempdb.

If a stored procedure creates temporary tables, the tables are dropped when the procedure exits. You can also explicitly drop temporary tables before a session ends.

Warning!   Do not create temporary tables with the “tempdb..” prefix from inside a stored procedure unless you intend to share those tables among other users and sessions.

Each time you restart SAP ASE, it copies model to tempdb, which clears the database. You cannot recover temporary tables.