11

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?

PhilBrown
  • 2,949
  • 7
  • 34
  • 53
  • see also http://stackoverflow.com/questions/1610/can-i-logically-reorder-columns-in-a-table and http://stackoverflow.com/questions/4402312/why-cant-i-reorder-my-sql-server-columns – Tim Abell Aug 06 '15 at 18:07
  • 1
    take table design and just drag the column accordingly – Athul Suresh Apr 06 '18 at 08:39
  • @Athul Suresh this solution may not work depending on your SQL Server configuration. I received an error stating I need to enable saving changes that require dropping and rebuilding the table. It could also create a permission issue for some. – Sherman Mar 08 '23 at 21:17

8 Answers8

11

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
AHiggins
  • 7,029
  • 6
  • 36
  • 54
8

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

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
  • 2
    not really, I was going through a tutorial that asked me to do so. I didn't bother because it's functionally useless, it just piqued my interest. – PhilBrown Mar 16 '11 at 15:37
6

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

ALTER table table_name ADD column column_name(new) datatype AFTER column_name
das-g
  • 9,718
  • 4
  • 38
  • 80
Shrikant Kapare
  • 109
  • 1
  • 1
2

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.

jfaa
  • 21
  • 1
1

yes. You can drag and drop it in design mode, or copy and paste it in edit table mode

in response to comments, yes, this is with SQL Server Management Studio through the UI, and under the hood, it's doing the drop/recreate you're trying to avoid, so no, you can't do it without the server doing it for you.

Beth
  • 9,531
  • 1
  • 24
  • 43
  • 3
    That will do a drop/recreate in the background. – Joe Stefanelli Mar 16 '11 at 15:29
  • I am assuming this is what you would do if you were using SQL Management Studio. Are you sure that SQL Server doesn't do a drop and re-create behind the scenes. I have got a feeling it copies the data to a temp table and then drops and recreates the table before copying the data back. I don't have access to SQL Server right now or I'd fire up SQL Profiler and try what you suggested. – Andrew Mar 16 '11 at 15:32
  • i was really looking for a way to do it in the ALTER TABLE statement, which is what SQL Management Studio does anyway. – PhilBrown Mar 16 '11 at 15:40
1

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.

Sivabalanarayanan L
  • 104
  • 11
0

First answer, no.

Second answer, according to this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58912, found via Can I logically reorder columns in a table?, you could create the column then reorder the columns in the table by editing the system tables. But it looks incredibly messy and I would not trust that things would not break.

Community
  • 1
  • 1
Tyler Clendenin
  • 1,459
  • 1
  • 12
  • 25
0

For Mysql yes For SQL server No also it's not a good practice to add column in between as it can hamper some queries if you are relying on the table schema in your project.

Pankaj Pahuja
  • 11
  • 3