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.)
[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
So there seem to be two workarounds/solutions:
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
answered 16 Mar '10, 10:30
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.
answered 12 Mar '10, 19:56
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.
answered 15 Mar '10, 13:49