how to add a column at a specific position in a table?

Question

Possible Duplicate:
SQL Server 2008 : Cannot Insert new column in the middle position and change data type

Hi i am using SQL SERVER 2008 & 2008 R2 i would like to add a column in a table at a specific position using alter command. is there any way keyword using which we can add a column at a specific position ? thanks! in adavanced.

**BTW:** The order of the columns is insignificant in the relational model, you shouldn't consider that order in your database design. — Mahmoud Gamal, Oct 01 '12 at 10:38
@MahmoudGamal - I would like the ability to be able to do this [for the reasons here](https://connect.microsoft.com/SQLServer/feedback/details/739788/alter-table-syntax-for-changing-column-order) — Martin Smith, Oct 01 '12 at 10:40

score 4 · Accepted Answer · answered Oct 01 '12 at 10:39

Not very easy in sql server..

I think the best way is to add the column at the end of the table and create a view with desired order of columns..

another option..

put all the data in a temp table and recreate the actual table with correct column order, then insert the data back to the actual table from temp table

Paul Aldred-Bann · Answer 2 · 2012-10-01T10:57:10.697

There is no quick way to do this in script, at best you can automate it with the following script that essentially takes your new table schema (with your new column at the desired location), inserts all data from old into new, drops the old table and then uses a stored procedure to rename the new table to old.

create table [NewTable]
(
    [old_column_1] int
    ,[new_column_1] varchar(max) -- new col at location
    ,[old_column_2] int
);

insert into [NewTable] ( [old_column_1], [old_column_2] ) 
select [old_column_1], [old_column_2] from [OldTable] (nolock);

drop table [OldTable];

sp_rename '[NewTable]', '[OldTable]';

As others have correctly pointed out though, the position of your columns makes NO difference whatsoever to the database, but there is a human desire to achieve this for various personal reasons and so you have a few options. You can either:

Use the script above to handle the process.
Allow the appending of your column to the end of the table and control the order in your query.
Basically an extension to option 2, use a view to control the order in your presentation.
Use the GUI in SQL Server Management Studio - you can drag and drop your column to the desired location when displaying your table as a grid.

how to add a column at a specific position in a table?

2 Answers2

Linked