Thread: Insert new column in the middle of a table

Question asked by visitor kiran

Can I insert a new column in the middle of an existing table?
Say I have created a table emp in order -> eno, ename, jobnow I want to insert a new column in between ename and job. If posble how?

In oracle, you can't do that. Between why do u need this? insert a new column in the middle of a table is not going to make any difference than appending a new column at the end.

could you tell us why you need this?

You can't do it but what you can do it is add at the end of the table with alter table add column statement !!!

That is not supported in oracle ,at least till version 10.2.0.

Actually it does not have any effect on data retrival and presentataion.

No u cannot insert a new column in middle of table but to the last is possible with a alter command

i too have the same problem can anybody sort it out

You can add a new column in the end, not in the middle.
inserting a new column in the middle of a table is not going to make any difference than appending a new column at the end.You can view that in middle using sql select statement by changing the sequence of columns as per your requirement.

you cannot insert column in the middle of the table.if u add any column to table it will appended to end.
if u want to appear in the middle, create another table by selecting columns from that table.

Originally Posted by kutty9585

i too have the same problem can anybody sort it out

Have you gone through the previous posts ?

yeah sure u can insert a new column in the middle, unfortunately it cannot be done by script, you can do it manually.
after you create the new column, open the design table, your column appears at the end, at the most left of the table, click on the column where an arrow appears, click on the arrow and place up the column where you want.
hope it helps.

U can't insert a new column in the middle of the table.
Oracle by default, any new column that inserted be at the end of the table.

the position of a column in an oracle table does not carry much value. The only problem is while selecting the columns with * the order will be not the desired one. however, this is till not solved by oracle.
We have to bear with it or the other option is to write the comple name list in desired order while selecting.

No we can not insert a new clumn in the middle of a table

Originally Posted by ronaldrahme

yeah sure u can insert a new column in the middle, unfortunately it cannot be done by script, you can do it manually.
after you create the new column, open the design table, your column appears at the end, at the most left of the table, click on the column where an arrow appears, click on the arrow and place up the column where you want.
hope it helps.

what design table you are talking about ?

It is not possible to insert a column in the middle of the table, But there is one way to do that in Oracle and SQL Server also. you can insert the column at the end and create a temp table in the reqired format and move the data from the original table to new table and replace the new file name with the old file name. By this way we can insert the column in the middle.
Hope it will help for you.

Yes, that is possible. Try with this

1. Create a new table and copy the data across.

SQL> RENAME tab1 TO tab1_old;
Table renamed.

SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old;
Table created

Also please visit this site orafaq . com/wiki/SQL_FAQ

That is not possible in oracle.But u can try the following

Just u create a view on that particular table and keep the order of the columns in your view as you required.And use that view when ever you have to work on your table.Using view means You are indirectly working on ur original table.

Oracle allow cols to be added only at the end of the existing table,bt some database allow cols to be added in middle of cols.for ex. In MYSQL following syntex is valid
[alter table table_name add colname after colname;]

No we can not insert a new clumn in the middle of a table in ORACLE.