on 2012 Apr 09 5:04 AM
Can you please tell me how to re-order the field positions in a table without having to re-create the table?
It seems it is not able to be done using SQLAnywhere 12.
Request clarification before answering.
Why would you like to re-order the column order?
There are some advanced technical reasons to do so (PKs should be among the first columns, variable-length or "wide" columns should be put at the end, more on this here...) for optimal performance, but in general the column order is rather irrelevant from the database system's point of view - IMHO it's usually more a human need to order columns "logically".
When you code a SELECT statement, you can obviously use whatever column order you like...
But otherwise, Thomas is absolutely right: There is no way to re-arrange the column order of an existing table (though this has beed discussed in the newsgroups in the past several times). So you will have to create a new table with the desired column order.
FWIW: Here's a link to a verrrry long discussion from 2006 on that topic in the "Product Futures newsgroup"...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Historically speaking, it might have been easier for Engineering to implement column reordering, perhaps as part of REORGANIZE TABLE, rather than endure endless discussions year after year... i.e., do it, rather than talk about not doing it 🙂
@Breck: Is that a subtle "product-suggestion-reminder"?
Sorry. But it is not possible.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
INSERT INTO TABLE A2 with auto name SELECT * from A1 ;
One might add that the very helpful "WITH AUTO NAME" clause is a SQL Anywhere vendor extension for INSERT ... SELECT, cf. the docs...
Depending on the version of SQL Anywhere the Create Table / Insert / and Select can be combined into one step.
However to answer your question simply, yes.
lets say you have table a1 with the old column order of a,d,c,g,b,e,f
and you have newly created table a2 with desired column order of a,b,c,d,e,f,g
You would perform the following:
INSERT INTO TABLE A2 ( a,b,c,d,e,f,g ) SELECT a,b,c,d,e,f,g from A1 ;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.