All Forums Tools
02 Aug 2012
Alter table to add column after specific column

Hi,

I have to add a new column into an existing table.

Is there a way to add a column after a specific column name. For example, my table has 20 columns and i have to add a column after the 10th column. Can someone tell the syntax for doing this ?

Your help is much appreciated. 

 

Thank you,

Prabhakar.T

robpaller 159 posts Joined 05/09
03 Aug 2012

ALTER TABLE will place any new columns at the end of the table definition. To place this column in the ordinal position on the table you will need to create a new table with the column in the proper ordinal position and then insert the data from the original table into the new table.

If you have employed any view layers in your data model then you can simply place this column in the ordinal position of the view definition and its location in the physical table become irrelevant.

09 Aug 2012

Thanks Rob.

WAQ 158 posts Joined 02/10
22 Aug 2012

Hi Prabhakar,

Just curious to know that why you are interested in the poistion of the new column? The position of the column in the table is irrelevant because you can always access them in any order you want.

taruntrehan 43 posts Joined 10/12
21 Feb 2013

Hi Prabhakar,
I am facing the same issue and reason for placing the column at same location is because of dependency on file feed layout.
If we add it in the end, the load process will start failing.

Regards,
Tarun Trehan
http://allzhere.in

KS42982 137 posts Joined 12/12
21 Feb 2013

You can always make INSERTs of your load scripts to match with the incoming file, it doesn't matter how the table structure is. I don't think of any example where you can face issue because of the order of columns in the table. Please share the script if possible, where you see issue because of this.

You must sign in to leave a comment.