Creating an ASE Nonsharable Temporary Table

When you save, run, or debug a stored procedure or trigger that references Adaptive Server Enterprise nonsharable temporary tables, the tables must be created in the session. You must create the tables explicitly at least once. After that, you can allow the tooling to create them automatically from SQL annotations inserted as comments in the procedure or trigger.

Prerequisites

Connect to an Adaptive Server Enterprise database.
Set defaults for temporary table creation in the Database Development > Miscellaneous preferences page.

Note: You cannot create a nonsharable temporary table from a SQL file. The procedure or trigger must be in an Adaptive Server Enterprise database.

Create a stored procedure or trigger and include a reference to a nonsharable temporary table that does not exist in the database.
Begin the table name with a pound sign (#) to indicate a nonsharable temporary table, for example, #temp1.

Perform an action that requires the temporary table to exist:

Option	Description
Save the procedural object to the database server	Right-click and select Save to Server.
Run the procedural object	Right-click and select Run.
Create the temporary table	Open the procedural object in its editor. In the Source page, right-click and select Adaptive Server Enterprise_15.x > Create Temporary Table.
Launch a Debug session	Right-click the procedure or trigger in Enterprise Explorer and select Debug . Configure parameters or variables as needed. The procedure or trigger opens.

All referenced temporary tables appear in the temporary table list.

Click in the Create column as needed to specify which tables to create.
By default, the tooling lists all tables that do not exist in the current session.

Choose a SQL template from the Templates for Creating Temporary Tables drop-down:

Option	Description
Create Table	Creates the table in tempdb but does not populate it with data.
Select Into	Creates the table in tempdb and populates it automatically. For large tables, select into is faster than create table and insert...select.

Note: For Save to Server to succeed, you must choose one of the templates to create the table.

Edit the SQL template in the SQL statement editor box.
You can enter edits manually and by choosing Content Assist from the context menu. Use Tab to navigate template variables.
Click Enter to exit template mode.
(Optional) Choose Insert the SQL Statement as Annotations (default and recommended).
With this option selected, the tooling can re-create the temporary table automatically in another session, so that you do not need to re-enter the table creation syntax.
Click Next.
The wizard validates the SQL syntax. If it finds any errors, the table with the error shows Invalid in the Status column and you must correct the SQL for that table before continuing.
Review the table creation syntax on the Summary of SQL Statements page.
(Optional) To return to the previous page and edit the SQL, press Back.
Click Finish to create the temporary table.
The table is created in tempdb for the current session. You can edit the table contents with the Table Data Editor, but changes are valid for the current session only.

If you selected Insert the SQL Statement as Annotations when the nonsharable temporary table was created, the table creation syntax appears in the editor in a comment under this warning:

/**

Warning: This annotation was generated by temporary table wizard. Do not edit it.

@TemporaryTableName #table_name

Once you have completed this setup, any time you run or save the stored procedure or trigger, if the referenced temporary tables do not yet exist in the current session, the tooling creates them by executing the commented syntax.