How to ALTER a table created with SELECT INTO LOCAL TEMPORARY TABLE...?

2

SA 11.0.1.2376

When I create a local temporary table by means of a SELECT INTO LOCAL TEMPORARY TABLE, I am not able to alter that table since ALTER TABLE returns a "table not found" error. Is this by design? If so, why?

(Business case: I often "copy" remote data into temp tables and prefer to omit the declaration of all columns and types – call it lazyness. However, I usually have to define PKs and the like afterwards.)

Example:

select * into local temporary table LT
from systable

select * from LT
-- works

alter table LT add test_col int null
-- returns "table LT not found"

asked 12 Mar '10, 18:13

Volker%20Barth's gravatar image

Volker Barth
39.3k●355●539●809
accept rate: 33%

edited 12 Mar '10, 18:18

One brute force workaround is to copy-and-edit the CREATE TABLE displayed by Foxhound or produced by dbunload. Not nearly as maintainable as SELECT * which does not have to be edited when the schema changes. Folks have asked, in the past, for the ability to "create this new table to look like that one", possibly in the old product futures discussion newsgroup.

(12 Mar '10, 20:01) Breck Carter

@Breck: I guess the even easier "brute force" method would be to use Sybase Central to mark the table and copy its schema. That needs just a few clicks/shortcuts:)

(12 Mar '10, 22:22) Volker Barth
4 Answers:
active answersoldest answersnewest answerspopular answers
2

[Note: Edited answer to reflect Breck's and Mark's responses:]

As Breck has explained, ALTER TABLE is not only forbidden for tables created by SELECT INTO TEMPORARY TABLE but for all those kinds of local temporary tables, i.e. it applies also to

  • SELECT INTO #TT
  • CREATE TABLE #TT
  • CREATE LOCAL TEMPORARY TABLE
  • DECLARE LOCAL TEMPORARY TABLE

So there seem to be two workarounds/solutions:

  1. If one relies on having an "auto-generated table" from a result set (ignoring performance drawbacks), one has to use a permanent table instead of a local temporary table, and drop that table afterwards. (That's what I have done in the past but forgot about lateron:(

    select * into PT from systable

    select * from PT -- works

    alter table PT add test_col int null -- works

  2. If performance does matter and one has to use a local temporary table for that reason, one should create the table explicitly with means of CREATE/DECLARE LOCAL TEMPORARY TABLE and then use INSERT INTO ... SELECT instead.

    In that respect, SQL Central is a convenient tool to copy the table schema.

permanent link

answered 12 Mar '10, 22:13

Volker%20Barth's gravatar image

Volker Barth
39.3k●355●539●809
accept rate: 33%

edited 15 Mar '10, 08:37

4

Note that there are drastic performance issues with creating/dropping permanent tables... DDL forces stored procedures to be dropped from in-memory cache and must be reloaded/parsed... cached query plans will also be dropped. This is necessary since adding/dropping tables can change how table references are bound.

(13 Mar '10, 00:38) Mark Culp
4

You can't use "ALTER TABLE" on a temporary table, but it's very well possible to do a "CREATE INDEX". I used that a few times already. 1. CREATE or DECLARE the temporary table 2. fill the table 3. create the indexes

permanent link

answered 16 Mar '10, 10:30

Markus%20D%C3%BCtting's gravatar image

Markus Dütting
536●4●12●20
accept rate: 30%

1

Interesting point - I wasn't aware of that. As one might expect, such an index isnt' shown in the system catalog, either, and can't be dropped with DROP INDEX but is dropped automatically when the table is dropped.

(16 Mar '10, 13:19) Volker Barth
2

ooooo... so why not? Why not implement ALTER TABLE and DROP INDEX and all those other things? So what if it's not in the catalog?

(16 Mar '10, 14:39) Breck Carter
2

Alas, the decision to not store information about local temporary tables in the system catalog was made a long time ago in a galaxy far, far away.

One side effect is this sentence in the Help: "You cannot use ALTER TABLE on a local temporary table."

Was it a good decision? If it contributed to the great speed of temp tables in SQL Anywhere, then yes, I think so.

Does that decision cause great pain? Oh, yes... from time to time... but there has not been much hue and cry over the years. Not nearly as much anguish as with other products; e.g., endless debates and discussions of the tempdb database in [this other product] and [that one].

Would it be difficult to change? Oh, gosh, I do think it would fall into the category of "changing basic architecture"... I recently had to do one of those kinds of changes, in Foxhound, not something you consider lightly :)

But... you're just asking for ALTER TABLE to work on local temporary tables. In theory, you could implement that without storing information in the catalog. In theory.

permanent link

answered 12 Mar '10, 19:56

Breck%20Carter's gravatar image

Breck Carter
32.5k●539●724●1050
accept rate: 20%

Thanks for the pointer, I really missed that sentence in the docs:) - The decision to exclude local temporary tables from the system catalog is reasonable. The introduction of ALTER LOCAL TEMPORARY TABLE might be a nice-to-have extension, but no more, I think.

(12 Mar '10, 22:01) Volker Barth
Comment Text Removed
1

If you know ahead of time that you need an extra column, why not add that column to the SELECT list of the query that created the temporary table? You can provide a value, cast it to the required data type, and provide an alias for the column name.

permanent link

answered 15 Mar '10, 13:49

Bruce%20Hay's gravatar image

Bruce Hay
2.6k●1●5●10
accept rate: 48%

Sorry for the confusion with the added column. The original requirement was to add PRIMARY and/or UNIQUE KEYs or indexes. I have chosen the "add column" example just because I thought it was more comprehensible. For additional columns, I generally do as you suggest, so that's no problem with local temporary tables:)

(15 Mar '10, 14:15) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×32
×26
×5

question asked: 12 Mar '10, 18:13

question was seen: 11,279 times

last updated: 16 Mar '10, 10:30

Related questions

What does SELECT * INTO LOCAL TEMPORARY TABLE ... actually do?

Enhance SELECT INTO LOCAL TEMPORARY TABLE to define a primary key

Generate Script for Table Data

what is wrong with the fetch loop?

Assertion failed: 200166

How to inquire Column Data Type

Save file to database

Table Permissions list

How can i get the size of an index in SQL Anywhere 10 or 16?

insert into temp table select .... query used in procedure hang