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

Question

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 1

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.

Answer 2

solution:

This will work for tables where there are no dependencies on the changing table which would trigger cascading events. First make sure you can drop the table you want to restructure without any disastrous repercussions. Take a note of all the dependencies and column constraints associated with your table (i.e. triggers, indexes, etc.). You may need to put them back in when you are done.

STEP 1: create the temp table to hold all the records from the table you want to restructure. Do not forget to include the new column.

CREATE TABLE #tmp_myTable (   [new_column] [int] NOT NULL, <-- new column has been inserted here!     [idx] [bigint] NOT NULL,     [name] [nvarchar](30) NOT NULL,     [active] [bit] NOT NULL ) 

STEP 2: Make sure all records have been copied over and that the column structure looks the way you want.

SELECT TOP 10 * FROM #tmp_myTable ORDER BY 1 DESC -- you can do COUNT(*) or anything to make sure you copied all the records

STEP 3: DROP the original table:

DROP TABLE myTable

If you are paranoid about bad things could happen, just rename the original table (instead of dropping it). This way it can be always returned back.

EXEC sp_rename myTable, myTable_Copy 

STEP 4: Recreate the table myTable the way you want (should match match the #tmp_myTable table structure)

CREATE TABLE myTable (   [new_column] [int] NOT NULL,     [idx] [bigint] NOT NULL,     [name] [nvarchar](30) NOT NULL,     [active] [bit] NOT NULL ) 

-- do not forget any constraints you may need

STEP 5: Copy the all the records from the temp #tmp_myTable table into the new (improved) table myTable.

INSERT INTO myTable ([new_column],[idx],[name],[active]) SELECT [new_column],[idx],[name],[active] FROM #tmp_myTable 

STEP 6: Check if all the data is back in your new, improved table myTable. If yes, clean up after yourself and DROP the temp table #tmp_myTable and the myTable_Copy table if you chose to rename it instead of dropping it.