ALTER TABLE - ADD column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

ALTER TABLE - ADD column

Author

Topic

kiwy
Starting Member

6 Posts

Posted - 2005-09-27 : 10:19:28

I have the table table_x:
col1 INT PRIMARY KEY
col2 VARCHAR

I want to add col3 between col1 and col2. In MySQL it's done with the following query:
ALTER TABLE table_x ADD col3 VARCHAR( 10 ) NOT NULL AFTER col1 ;

In sql server all i can do is to add the column to the end of table. Is there a way to insert a new column in the middle or to move a column to left/right?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-27 : 10:20:45

You can use Enterprise Manager
Actually the position of column in the table does not matter

Madhivanan

Failing to plan is Planning to fail

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2005-09-27 : 10:34:06

quote:
Originally posted by madhivanan

You can use Enterprise Manager.....

If you use EM for this purpose, sql server will drop and re-create the table. There are some disadvantages of this approach, which i am not able to recollect at the moment.

------------------------
I think, therefore I am - Rene Descartes

kiwy
Starting Member

6 Posts

Posted - 2005-09-27 : 10:44:09

i needed the script so i can update all the deployed databases, not only the developement one.
i know i can do it from EM.
the column orders probably doesnt matter from performance point of view, but from the readabilty of the database diagram it counts big. i'm tring to have the logical schema as the phisical one.

don't take it to hard on me, but mysql knew how to do this ;)

Kristen
Test

22859 Posts

Posted - 2005-09-27 : 10:51:10

Hi kiwy, Welcome to SQL Team!

"i needed the script so i can update all the deployed databases"

I do it in EM, press the "Script" button [it looks like a scroll], and then abandon the change - and then use the script to actually make the change - thus I will be doing the same thing when I roll out.

Beware that the Script button will NOT include COLLATE settings for varchar etc. columns which use the default database settings (whereas the its Big Brother "Generate SQL" to script the whole database WILL include COLLATE stuff)

Kristen

kiwy
Starting Member

6 Posts

Posted - 2005-09-27 : 11:08:35

thanks.
problem solved :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources