Temporary Table in Sybase Sql Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL - Part 13 Division 5

Introduction This is part 13 of my series, Handling Sybase Events with Triggers and Stored Procedures Using SQL. This series is the fifth division of my larger series, Database. You must have read all the different parts of the big series (or equivalent tutorials from elsewhere) up to this point. In this part of the series, we look at temporary tables in Sybase SQL Anywhere 12.

Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at That is, contact me for the slightest problem you have about what you are reading.

Local Temporary Table A temporary table is a table that is saved in a temporary file. A temporary file is a file that will be deleted soon. There are two types of temporary tables: local temporary tables and global temporary tables. In this series we shall deal only with local temporary tables.

A local temporary table is for a connection. It exists as long as the connection is on. If the temporary table is defined inside a compound statement, it will exist as long as the compound statement is operating. You can create a local temporary table for a connection or you can declare a local temporary table in a compound statement.

Local Temporary Table for a Connection You create a temporary table for a connection in the same way that you create a base table, but you precede the table name with the pound sign, #. A simplified syntax is:

CTREATE TABLE #table-name ( { column-definition [ column-constraint ... ] | table-constraint | pctfree }, ... )

Try the following:

CREATE TABLE #Women ( WomanID INTEGER DEFAULT AUTOINCREMENT, Name CHAR(40) INDEX, Address CHAR(60), City CHAR(30), State CHAR(30), Country CHAR(40), Phone CHAR(12),

PRIMARY KEY (WomanID ASC) );

Local Temporary Table for a Compound Statement To create a local temporary table in a compound statement use the syntax (simplified):

DECLARE LOCAL TEMPORARY TABLE table-name ( { column-definition [ column-constraint ... ] | table-constraint | pctfree }, ... )

Now this table will cease to exist when the program is not operating in the compound statement.

So to have a local temporary table in a procedure, you should use the above DECLARE statement. However, if you want a local temporary table in a procedure that would be for the connection (last after the procedure completes), use the following syntax (simplified):

CREATE LOCAL TEMPORARY TABLE table-name ( { column-definition [ column-constraint ... ] | table-constraint | pctfree }, ... )

Note: You cannot use the REFERENCES column-constraint or the FOREIGN KEY table-constraint on a local temporary table.

A temporary table is used like other tables, but you do not want the table saved. We shall see a practical use of this later.

Well, let us end here for this tutorial. We continue in the next part of the series.

Chrys

To arrive at any of the parts of this division, type the corresponding title below in the search box of this page and click Search (use menu if available).

Handling Sybase Events with Triggers and Procedures Using SQL - Part 1 Handling Sybase Events with Triggers and Procedures Using SQL - Part 2 Handling Sybase Events with Triggers and Procedures Using SQL - Part 3 Handling Sybase Events with Triggers and Procedures Using SQL - Part 4 Handling Sybase Events with Triggers and Procedures Using SQL - Part 5 Handling Sybase Events with Triggers and Procedures Using SQL - Part 6 Handling Sybase Events with Triggers and Procedures Using SQL - Part 7 Handling Sybase Events with Triggers and Procedures Using SQL - Part 8 Handling Sybase Events with Triggers and Procedures Using SQL - Part 9 Handling Sybase Events with Triggers and Procedures Using SQL - Part 10 Handling Sybase Events with Triggers and Procedures Using SQL - Part 11 Handling Sybase Events with Triggers and Procedures Using SQL - Part 12 Handling Sybase Events with Triggers and Procedures Using SQL - Part 13 Handling Sybase Events with Triggers and Procedures Using SQL - Part 14 Handling Sybase Events with Triggers and Procedures Using SQL - Part 15 Handling Sybase Events with Triggers and Procedures Using SQL - Part 16

iAutoblog the premier autoblogger software

Related Post: