|
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. |
|
Sorry. But it is not possible. Is it able to select fields from old_table (as corrected field order) and insert to new table (as creation of new table) Thanks
(09 Apr '12, 04:54)
Kongthap
Replies hidden
One might add that the very helpful "WITH AUTO NAME" clause is a SQL Anywhere vendor extension for INSERT ... SELECT, cf. the docs...
(11 Apr '12, 06:47)
Volker Barth
|
|
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"... 2
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 :)
(09 Apr '12, 17:19)
Breck Carter
Replies hidden
@Breck: Is that a subtle "product-suggestion-reminder"?
(11 Apr '12, 03:24)
Volker Barth
|
|
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 ; |
