Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

[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
40.2k361549822
accept rate: 34%

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.7k53870
accept rate: 40%

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

permanent link

answered 22 Jun '23, 09:39

Volker%20Barth's gravatar image

Volker Barth
40.2k361549822
accept rate: 34%

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:

×113
×27
×5

question asked: 29 May '11, 16:57

question was seen: 3,655 times

last updated: 22 Jun '23, 09:39