|
[Note: This is a suggestion I had already put in a comment to this answer by Karim. - It seems worthwhile to put this in its own question, methinks...] When using the SELECT INTO LOCAL TEMPORARY TABLE syntax to automatically create a temporary table based on the result set, it would be handy if one could define the PK automatically, too. As stated in this question, that's not currently possible, as local temporary tables cannot be ALTERED afterwards to ADD a PK or anything else. (Creating an index is possible but somewhat cumbersome IMHO). In my experience, I mostly use this syntax to create some kind of schema-agnostic copy of a table - often of a proxy table. Therefore a separate CREATE ... TABLE statement with an explicit PK definition would work but would contradict the goal to omit the explicit table definition. The one thing I usually want to add lateron is the PK. - Note that adding FKs would be quite useless as they are forbidden between temporary and permanent tables AFAIK. So I'd suggest a clause like SELECT * INTO LOCAL TEMPORARY TABLE MyTempTable (WITH PRIMARY KEY) FROM ... that would auto-create a PK for MyTempTable if the result set has a unique row (and fail if none or more than one are available). AFAIK sa_describe_query allows for such tests... Alternatively, one might specify the wanted PK column in the clause... |
|
Volker, Thank you for your suggestion. I am quite sure the problem is much more involved than it would first appear; but your suggestion is still very valuable. I have, therefore, requested that an enhancement request be logged with the information from your post above. Karim Thanks again, Karim:)
(31 May '11, 08:39)
Volker Barth
|
|
A late answer: With v17, the CREATE/DECLARE LOCAL TEMPORARY TABLE statements with the new LIKE clause comes in handy to build local temporary tables based on (joins of) other tables/views and addional fields and/or contraints in a schema-agnostic fashion, such as a modified version of my old sample: DECLARE LOCAL TEMPORARY TABLE MyTempTable LIKE MyPermanentTable INCLUDING PRIMARY KEY NOT TRANSACTIONAL; INSERT MyTempTable FROM MyPermanentTable WHERE... -- or DECLARE LOCAL TEMPORARY TABLE MyTempTable (LIKE MyPermanentTable, PRIMARY KEY(MyID)) NOT TRANSACTIONAL; INSERT MyTempTable FROM MyPermanentTable WHERE... |