Adding a column immediately after a specific current column

Transact-SQL
1

Using mssql2017. Need to add a new column after an existing column. Tried below syntax , but its not working

Alter table mytable add mynewcolumn varchar(255) after myothercolumn

Any help is appreciated.
Thank you

2

When you add a column to an existing table, it is added as the last column, i.e., the last ordinal position. The only way to add a column between existing columns is to drop and recreate the table.

Code that relies on ordinal positions, in general, is not a good practice. The recommended practice is to always explicitly list the columns. That said, I know many situations where it would be useful to do what you are asking for, but unfortunately, SQL Server does not allow that.

1 Like
3

HI.
Why would you want to do such a thing?
In SQL Server you cannot add a column at a specific location. You should play dirty with Management Studio (but it's not always possible) or by creating a new table and moving the data into it.
However, in general I advise against doing this. You simply create a view that gives you the columns in the order you want.

4

If you use SSMS (the SQL gui) to change the table, you can add the column wherever you want. SSMS will generate a script to recreate the table with the columns in the order you want. WARNING: if the table is small, this will work fine. For a large table, it might time out or take a very long time.

If the table is (very) large, the only other way would be to create a new table with the new columns in the order you want, then copy the existing table into the new table (then finally drop the old table).

1 Like