The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 29 May '11, 16:57

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 29 May '11, 17:03


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

permanent link

answered 31 May '11, 07:50

Karim%20Khamis's gravatar image

Karim Khamis
5.6k53870
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

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

Question tags:

×106
×18
×5

question asked: 29 May '11, 16:57

question was seen: 1,472 times

last updated: 31 May '11, 08:39