Add column in the midle of the table in Sybase

Hi,

I want to add a column between two columns in a table in sybase. I know when I add a column it adds on the last .
Is there any way ( except dropping and recreating the table and using any third party tool) to do it in sybase 12.0 onwards or its higher versions .

Regards,
Asit
asitbasak85Asked:
Who is Participating?
 
leonstrykerCommented:
No, not really, but why do you want to do this?

Leon
0
 
grant300Commented:
The only time the order of appearance of columns in a table might matter is when building a clustered index or key and then only the indexed columns are involved.

Leon is right; there is no way to wedge a column in the middle of a table but there is a cleaner way than dropping and recreating the table.

Do a SELECT/INTO to create a copy of the table by another name being sure to add the new column in the middle when you do it.  Then rename the original table to get it out of the way and rename the new table to the original name.  You will then have to go through and add the indexes, views, and triggers back onto the table but you saved the problems/risk associated with a straight drop and recreate.  This is, by the way, the method that the third party tools like RapidSQL use to accomplish this kind of table change.

Don't forget to update statistics and run sp_recompile to make sure all the stored procs are back in shape.

Regards,
Bill
0
 
PeretzBenRafaelCommented:
 Based on previous experience, grant300 is correct with one caveat.  You must drop the original table before executing / recompiling any stored procedures.  Having the original table still in the database is dangerous.

  Procedures work with the object_id of the tables, not the name.  If a procedure was ever executed using the old table, it will still find the old one no matter what you rename it to.  I've experimented with this and demonstrated it to my own satisfaction.

  An additional, unasked-for comment on style:  The order of the columns should not matter / be relied upon in any code that will be put into production.  SELECT statements that return data to the calling application should have a list of field names unless they are coming from an intermediate layer that maps each field into an internal variable identified by field names (does anybody remember JAM?  It handled SELECT * nicely).

  INSERT statements should always be of the form INSERT INTO table (col1, col2, col2) VALUES (val1, val2, val3).  Otherwise, changing the order of fields in a table can lead to disastrous results.
0
 
LowfatspreadCommented:
create a view on the table ordering the columns as you desire....

however this implies that you're using select *
which should be frown upon in any production code...

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.