7

I have Table A with 5 columns:

TableA
--
Name
Tel
Email
Address

I want to add a new column (mobile) in between Tel & Email:

TableA
--
Name
Tel
Mobile
Email
Address

If I use 

ALTER TABLE TableA
ADD COLUMN Mobile INT NOT NULL

the mobile column is added to the end of the table.

Is there a way to achieve this without dropping the table and moving data to a new table?

Improve this question
4
  • 5
    The order of columns in a relational table does not matter. Why do you think you need to have a specific position for that column? You can always get the column order you need by specifying that order in the list of columns for the select statement
    – user1822
    Commented Jun 20, 2012 at 6:54
  • 2
    True, i just want the table to look structured and organised, i mean i want related columns to be near each other, like firstname,lastname i don't want first name in beginning and last name at the end.
    – AmmarR
    Commented Jun 20, 2012 at 6:58
  • 2
    @AmmarR - FWIW I agree with you and Erland's comment on the linked connect item. It is much nicer to have related columns together when viewing the table in the designer than having to jump around the table definition.
    – Martin Smith
    Commented Jun 20, 2012 at 7:54
  • Why we can't insert a new column between existing columns of a table: connectsql.blogspot.com/2010/12/…
    – Aasim Abdullah
    Commented Jul 18, 2012 at 17:26

2 Answers 2

Reset to default
17

No. Rebuilding the table is the only way. See this Connect Item for confirmation.

You could use SSMS to script this for you if you trust the somewhat buggy table designer.

Apart from that you could declare a view with the desired column order as a way of grouping logically related columns together.

Improve this answer
2
  • Sad truth because this table is huge, 30 million records and its heavily used. i am a bit surprised about this i mean in Sql 2000 i was able to use the Table designer to add a column in between columns and the designer allows saving changes, but in sql 2008, i always get the message Saving changes is not permitted, the changes you have made require the table to be dropped and re-created that's way i use script to do that, i don't know what made Microsoft to change their mind about this feature.
    – AmmarR
    Commented Jun 20, 2012 at 6:45
  • 9
    @AmmarR - The only new thing is that now you get a warning message, before it just dropped and recreated the table silently behind the scenes. 2000 had no syntax to do this either. You can disable the option to prevent saving changes that would drop and recreate the table but I definitely wouldn't bother in the case you describe above unless you can do it in a maintenance window. You should also be aware that the designer has some bugs (e.g. loses the FILESTREAM on columns when it recreates the table) so just use that to script the change then sanity check it.
    – Martin Smith
    Commented Jun 20, 2012 at 7:05
-8

If you see "Saving changes is not permitted." messages after adding a new column in Designer with SQL Server 2005 or greater, you'll need to select Tools -> Options, select Designers, and deselect the box under Table Options named: "Prevent saving changes that require table re-creation." After you do this step, you should be able to save your changes where you want.

It is strange that MySQL allows you to add columns anywhere using SQL, but with SQL Server, you can only do this using Designer. Hmmm. Seems like there must be some hidden magic commands somewhere! ;-)

Improve this answer
1
  • 1
    This would still drop the table.
    – ypercubeᵀᴹ
    Commented Jan 28, 2015 at 16:33

Not the answer you're looking for? Browse other questions tagged or ask your own question.