[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


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...

asked 29 May '11, 16:57

Volker%20Barth's gravatar image

Volker Barth
accept rate: 32%

edited 29 May '11, 17:03


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.


permanent link

answered 31 May '11, 07:50

Karim%20Khamis's gravatar image

Karim Khamis
accept rate: 40%

Thanks again, Karim:)

(31 May '11, 08:39) 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



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

Question tags:


question asked: 29 May '11, 16:57

question was seen: 1,497 times

last updated: 31 May '11, 08:39