Add column in the midle of the table in Sybase

Posted on 2007-07-30
Medium Priority
Last Modified: 2012-06-27

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 .

Question by:asitbasak85
LVL 29

Accepted Solution

leonstryker earned 500 total points
ID: 19592770
No, not really, but why do you want to do this?

LVL 19

Assisted Solution

grant300 earned 500 total points
ID: 19593006
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.


Assisted Solution

PeretzBenRafael earned 500 total points
ID: 19593456
 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.
LVL 50

Assisted Solution

Lowfatspread earned 500 total points
ID: 19593623
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...


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
Tech giants such as Amazon and Google have sold Alexa and Echo to such an extent that they have become household names. And soon they are expected to be used by commoners in their homes, ordering takeout, picking out a song, answering trivia questio…
Integration Management Part 2
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question