Adding column in between a table

sunnyk · New User Joined: 20 Oct 2004 Posts: 59

Hi all,
Suppose there are 10 columns in a table and i want to insert a new column between position say 5 and 6.How to do it?
I think we can`t use alter table command for this.

Is this a solution:
Taking image copy of the table and then dropping a table,redefinining and then reloading with new columns(now 11)?????
If there is any simple solution please update me.

jz1b0c · Posted: Wed Dec 08, 2004 10:59 pm

yes what you said is the solution, the details process of this, I have answered before.

ovreddy · Posted: Thu Dec 09, 2004 11:14 am

Hi,

You can do this in the following way...

1. Add new column using alter table that will be added at last position(11th)

2. Create a view by taking the above table as base table and 11th column as 5th column.
3. Use the view instead of table thats it.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi,
I couldn't really understand.
In which situation you would like to insert a column between existing columns. Can we live with ALTER TABLE to add a new column at the end.?

karthi1 · New User Joined: 07 Jul 2006 Posts: 2 Location: Bangalore

Hi Parikshit,

There are situations where u need to add column in the middle of the table.The best solution is given by sunny itself which is to take backup,drop and recreate the table.For adding a new column at the end can be dont using ALTER TABLE statement itself.

parikshit123 · Active User Joined: 01 Jul 2005 Posts: 269 Location: India

Hi,

I really can't think of any situation wherein you might need to add a column between two columns.

I would appriciate if you can describe at least one situation.

well, solution described by ovreddy seems fine. But there is a problem in that. What if we want to update that view? Please note that not all the views are updatable.

Thanks,
parikshit

honeypria567 · New User Joined: 20 Nov 2006 Posts: 14 Location: chennai

hi,

The best solution is given by sunny and it is the safest way to add the

column.

dharmendra_kp · New User Joined: 10 Mar 2005 Posts: 33 Location: Lewiston

I too agree with Parikshit. How does it matter if you add the column at the end or b/w 5th & 6th column. I don't think now a days any of the program does SELECT * FROM TAB. Yes I have seen some utility jobs that unloads the table containt in a PS file that file using SELECT *, that may in turn be used as input to another pgm here too you need to just change the file structure such that you place the new column field at end.

dharmendra_kp · New User Joined: 10 Mar 2005 Posts: 33 Location: Lewiston

If anyone visualizes a situation wherein you need to put a column b/w existing columns please let the forum know.

Thanks.

ragshere · New User Joined: 20 Dec 2004 Posts: 70

sunny,
Unload the data (instead of taking the image copy).then drop and recreate the table incase you want to add new column in the middle.

Image copy won't be useful in this case since table structure got changed.

Regards
Rags

mkk157 · Active User Joined: 17 May 2006 Posts: 310

Hi Dharmendra,

Here I am visualizing one simple situation where it is necessary to insert a new column between the existing columns.

Let us consider that we are areating a EMPLOYEE table. In that the required fields are as follows:

First_name
Last_name
Join_Date
Emp_ID
Experience
Salary
Mail_Id
Comm_Address
Parmanent_Address
Cell_Number.

I have created the EMPLOYEE table with these fields, After some time I got confused from the huge database I like to add Middle_name in between First_name and Last_name. In this situation we can't put this field in the last postition. We need to insert this column only at the 2nd postition. Then only it looks good.

If anything wrong in my answer please correct me.

DavidatK · Posted: Thu Jan 11, 2007 3:14 am

mkk157,

One of the real beauties of DB2 is the independence between the users/programmers view of the data, and the physical layout of the data. There is no real reason to insert a column in between two existing columns, in the physical sense. As ?ovreddy? suggested, the correct way is to create a view of the order you want the columns returned in, and then use that. As a user/programmer, you should not encumber yourself with the physical properties of the table.

William Thompson · Posted: Thu Jan 11, 2007 3:54 am

Dave,