Alter table to add column after specific column

Tools
Enthusiast

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

5 REPLIES
Enthusiast

Re: Alter table to add column after specific column

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.

Enthusiast

Re: Alter table to add column after specific column

Thanks Rob.

WAQ
Enthusiast

Re: Alter table to add column after specific column

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.

Enthusiast

Re: Alter table to add column after specific column

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.

Enthusiast

Re: Alter table to add column after specific column

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.