Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 –  a_horse_with_no_name Jun 20 '12 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 Jun 20 '12 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 Jun 20 '12 at 7:54
    
Why we can't insert a new column between existing columns of a table: connectsql.blogspot.com/2010/12/… –  aasim.abdullah Jul 18 '12 at 17:26

4 Answers 4

up vote 13 down vote accepted

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.

share|improve this answer
    
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 Jun 20 '12 at 6:45
7  
@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 Jun 20 '12 at 7:05

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! ;-)

share|improve this answer

Managment Studio has button generate change script. It will generate script to INSERT column to you table anywhere. Hope it help.

share|improve this answer

ALTER TABLE TableA ADD COLUMN Mobile INT NOT NULL AFTER Tel

This worked for me in MySQL hope that helps.

share|improve this answer
2  
thanks @asachanfbd, but AFTER doesnt work with Microsoft SQL server –  AmmarR Jul 20 '12 at 22:41
1  
-1 since this does not answer the question as it does not work in Microsoft SQL Server. –  Max Vernon Oct 10 '12 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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