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, you must create the tables in the current Sybase WorkSpace session. Once you explicitly create the tables, WorkSpace creates them automatically for future sessions from SQL annotations inserted as comments in the procedure or trigger.

1. 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.

2. Perform any of these actions to display the Temporary Table Creation wizard:

   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_12.x/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.

   The New Temporary Table wizard lists all the referenced temporary tables in the stored procedure or trigger that do not exist in the current session, and by default will have it checked indicating that it will be created.

   By default, WorkSpace lists all tables that do not exist in the current session.

3. 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, choose one of the templates to create the table.
4. Edit the SQL template in the SQL statement editor box.

   Enter edits manually and/or right-click and choose Content Assist. Use Tab to navigate template variables when using content assist.

   The creation and insert statements appear under these headings:

   --Add index creation statement below --Add insert statement below
5. Click Enter to exit template mode.
6. (Optional) Choose Insert the SQL Statement As Annotations (recommended).

   This option re-creates the temporary table automatically in another session, so that you do not need to reenter the table creation syntax.

7. Click Next.

   The wizard validates the SQL syntax. If it finds any errors, correct the SQL for that table before continuing.

8. Review the table creation syntax on the Summary of SQL Statements page.
9. Click Finish.

   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.