Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Adding column between two other columns in SQL server

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?

like image 396
PhilBrown Avatar asked Mar 16 '11 15:03

PhilBrown


5 Answers

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
like image 112
AHiggins Avatar answered Oct 23 '22 23:10

AHiggins


This is possible in SQL Server Management Studio (SSMS). Filter your table in Object Explorer and right click on the table > Design

enter image description here

Drag the arrow highlighted in the left to move your column.

like image 29
siva balanarayanan Avatar answered Sep 22 '22 14:09

siva balanarayanan


The simple answer is no. Is there a reason why column order is important to you?

like image 8
Joe Stefanelli Avatar answered Oct 23 '22 23:10

Joe Stefanelli


Yes you can add here is the query for your concern:

ALTER table table_name ADD column column_name(new) datatype AFTER column_name
like image 4
Shrikant Kapare Avatar answered Oct 24 '22 01:10

Shrikant Kapare


Take a look at this link:

http://www.bobsgear.com/display/ts/Adding+Column+After+Another+Column+-+SQL+Server+2005

As you can see, the answer is:

'not possible without moving data to a temp table'

which is what the SQL Server Management Studio actually does.

like image 2
jfaa Avatar answered Oct 23 '22 23:10

jfaa