Add a column to a table

[BigalYoung]

This should be fairly straightforward (I hope) !!

I need to add a new column to an existing table. If I use the simple

ALTER TABLE tabname
  ADD newcolumn SMALLINT NULL

then the column gets added as the last field. I need to be able to add the field at a specific point within the table i.e. make the new column the second field of a five field table.

 

[JayKusch]

WHY???? You should never be set on how the field layout is ordered. What are you gaining by having this field in that particular position?

If that is a set requirement, you are going to have to create another table with the fields ordered in the desired fashion you are needing, new field included, and then you need to export the data from the old table to the new table. Once that is done, drop/delete the old table and rename the new table back to the name of the table that was just dropped. NOT THE PREFERED METHOD THOUGH!!!

There is now way w/ an Alter Table command to stick a field in between other fields that have already been defined. I am sure others may have comments on this issue too. Good Luck and Enjoy!



Thanks

J. Kusch

 

[bernardmanning]

If the order is important, why not create a view on it and have your users access the view rather than the base table?

eg

Create View SomeViewName as select field3, field1 , field2

etc

Berny....

 

[jby1]

You can insert a new column at a chosen point in the table through SQL Query Analyzer. Although as James says, why would you care what order the column names are specified in?

 

[SQLBill]

jby1,

You cannot add a column at a chosen point using QA. You CAN do it via Enterprise Manager. However, all EM does is the same thing Jay described - EM just does it all for you.

-SQLBill

 

[jby1]

Sorry, I mean Enterprise Manager, my mistake!

I wasn't aware of how it actually did it behind the scenes though, thanks for that.

 

[SQLSister]

As SQL Bill said, you can do this in Enterprise Manager, but be wary of doing it with a table which has lotsof records, the change is likely to time out.

You can access the columns any way you want in a query or view and users should NEVER have direct access to the table, so why on earth would anybody care about the order of columns in the table?

Additionally doing this may very well break some of your code if you are doing updates or unions without specifying the columns. (Not that I recommend not specifying the columns, just that I recognize that this is often done.)