Temporary Table in Sybase SQL Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL – Part 13

Division 5

Forward: In this part of the series, we look at temporary tables in Sybase SQL Anywhere 12.

By: Chrysanthus Date Published: 28 Aug 2012

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 forchatrans@yahoo.com. 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

Broad Network

Related Articles

Temporary Table in Sybase SQL Anywhere

Handling Sybase Events with Triggers and Procedures Using SQL – Part 13

Division 5

Introduction

Related Courses

Comments