Alter Table - Add a field to a table in a specific location

I'm using the command to add a field to my table which has 20 fields.

ALTER TABLE table_name ADD column_name datatype

This adds the new field to the bottom of the table as the 21st first. How can I make it so it shows up as the 5th field in the table?

Wow you would have to do some work to get that to happen. It can be done but why?

The quick and dirty way is to copy all records into a temp table. DROP and reCREATE the table with the fields in the order of your preference. Then import the records from the temp table.

Adamus

|||The reason why I want to add it to a specific spot is to keep my table organized. The field that I am adding is a status field and I want it to be next to the other status fields and not just put it randomly at the bottom.

When you modify tables in Enterprise Manager it's real easy to change the location of fields...simply by drag and drop. This leads me to believe that there's a sql command that will do the same thing I'm just not sure what the command is.|||

Behind the 'scenes', Enterprise Manager does just like Adamus indicated. It creates a temp table, transfers the data, drops the old table, and renames the temp table.

There is no 'magic' to Enterprise Manager -it just writes the code for you -and sometimes not the best code either...

|||

Bank5,

this order is actually important only for the human user, as applications do not care so much about it (at least SHOULD NOT). Why don't you just create a view with correct column order which you can later use instead of table? Garnet Chaney posted an article about this, you can read it here

Also, "ALTER TABLE syntax for changing column order" feature is considered for the next release - if you think it would be useful you can vote here.

Cheers,

michalz