Adding column between two other columns in SQL server

PhilBrown picture PhilBrown · Mar 16, 2011 · Viewed 54.3k times · Source

Can you add a column to a table inserting it in between two existing columns in SQL Server without dropping and re-creating the table?

Answer

AHiggins picture AHiggins · Sep 15, 2014

Mediumly long answer, yes (ish) but it's ugly and you probably wouldn't want to do it.

please note: this code creates a physical table

CREATE TABLE MyTest (a int, b int, d int, e int)

INSERT INTO MyTest (a,b,d,e) VALUES(1,2,4,5)

SELECT * FROM MyTest

ALTER TABLE MyTest ADD c int
ALTER TABLE MyTest ADD d_new int
ALTER TABLE MyTest ADD e_new int

UPDATE MyTest SET d_new = d, e_new = e

ALTER TABLE MyTest DROP COLUMN d
ALTER TABLE MyTest DROP COLUMN e

EXEC SP_RENAME 'MyTest.d_new', 'd';
EXEC SP_RENAME 'MyTest.e_new', 'e';

SELECT * FROM MyTest 

DROP TABLE MyTest