Add Column between two other columns

At least there will be one question in my mail box about how to add a column in between two existing columns in a table???

There is no direct query/script to achieve this becoz in general, there is no significance for the order of the columns in the table.

If you really need this then you can try the below apporach

Lets assume that you want to add ENAME column to the EMP table in between EMP_ID and SALARY.

1. Add the the new column to the existing table say EMP
now the order will be EMP_ID,SALARY,ENAME

2. Create a new table EMP_NEW with the desired table structure and column order

3. Insert values from your existing EMP to this EMP_NEW

4. Drop the existing table EMP and rename EMP_NEW to EMP.


You can do the above steps only when no one is performing any DML operations on the table to avoid any missing rows.