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.
You cannot create a nonsharable temporary table from a SQL file. The procedure or trigger must be in an Adaptive Server Enterprise database.
To create a nonsharable temporary table:
Complete the setup:
Connect to an Adaptive Server Enterprise database.
Set defaults for temporary table creation in the Database Development|Miscellaneous preferences page.
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:
While the procedure or trigger is open in SQL Editor, choose Save to Server from the SQL Editor context menu.
While the procedure or trigger is open in SQL Editor, choose Run from the SQL Editor context menu.
While the procedure or trigger is open in SQL Editor, choose Adaptive Server Enterprise_15.x|Create Temporary Table from the SQL Editor context menu.
You can also choose this option from Database Tools in the main menu bar.
Launch a Debug session. Right-click the procedure or trigger in Database Explorer and choose Debug Procedure or Debug Trigger from the context menu.
Configure parameters or variables as needed. The procedure or trigger opens in the Debug view in Database Debug.
The New Temporary Table wizard 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:
Templates for creating temporary tables
| SQL statement | Explanation |
|---|---|
| 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. |
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 recreate the temporary table automatically in another session, so that you do not need to reenter 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 Insert the SQL statement as annotations was checked 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.
Completing the Setup for Database Development
Setting Miscellaneous Database Development Preferences
Saving a Procedural Object Definition to a Database
Send your feedback on this help topic to Sybase Tech Pubs: pubs@sybase.com