How to add a column in TSQL after a specific column?

tsql alter-table
Justin808 picture Justin808 · Oct 28, 2011 · Viewed 102.6k times · Source

I have a table:

MyTable
    ID
    FieldA
    FieldB

I want to alter the table and add a column so it looks like:

MyTable
    ID
    NewField
    FieldA
    FieldB

In MySQL I would so a:

ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID;

One line, nice, simple, works great. How do I do this in Microsoft's world?

Answer

Mike M. picture Mike M. · Oct 28, 2011

Unfortunately you can't.

If you really want them in that order you'll have to create a new table with the columns in that order and copy data. Or rename columns etc. There is no easy way.

Related questions

Altering a column: null to not null

I have a table that has several nullable integer columns. This is undesirable for several reasons, so I am looking to update all nulls to 0 and then set these columns to NOT NULL. Aside from changing nulls to 0, data must …

Read More
Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More
How do you change the datatype of a column in SQL Server?

I am trying to change a column from a varchar(50) to a nvarchar(200). What is the SQL command to alter this table?

Read More