How to add a new column

[darkradar]

Hi

I want to add a new column to a table through Query Analyzer. This is the code I am using for addring a
column.
***************
alter table store add city char(15);
***************
The above query is creating a column at the end.
Above table has 10 columns.
I want to add the city column after address column which
is 5th column. Is is possible to create a column at
a specific position through query analyzer.
I could do this one with Enterprise Manager .
But I want to do it through query analyzer as I have to
execute the same file in many other stores also.
Any help will be highly appreciated.
Thanks in advance.

 

[JayKusch]

To do it in query analyzer, you would have to export the data, drop the table, recreate the table w/ the new field in the correct position and then reimport the data making sure you use the correct syntax to put the exported data back into the right fields.

Thanks

J. Kusch

 

[SQLSister]

Which incidentally is what Enterpise Manager is doing. Personally I would never worry about the placement of a column inthe original table. Worry about that in your selects or views. Never make code dependdent on the order of columns ina table as someone could change them easiliy in Enterprise Manager and break your code.

 

[JayKusch]

I agree TOTALLY with SQLSisters comment on NOT developing code that is depenant on the location of a field in a table.

Thanks

J. Kusch

 

[mrdenny]

If you do decide that you really want the column in the middle:

Make the change in EM, and instead of clicking save click the button 2 buttons to the right of the save button. It says Save Change Script.

This will show you the script that EM is going to run on the SQL Server. You can then save it to a text file for easy running on the other servers.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[mrdenny]

If you do decide that you really want the column in the middle:

Make the change in EM, and instead of clicking save click the button 2 buttons to the right of the save button. It says Save Change Script.

This will show you the script that EM is going to run on the SQL Server. You can then save it to a text file for easy running on the other servers.

But as Jay and SQLSister said, column position doesn't really matter. And your code should be independent of the column order.


Denny

--Anything is possible. All it takes is a little research. (Me)