Add column in the midle of the table in Sybase

Posted on 2007-07-30
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

    No, not really, but why do you want to do this?

    LVL 19

    Assisted Solution

    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.

    LVL 1

    Assisted Solution

     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

    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...


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    These days socially coordinated efforts have turned into a critical requirement for enterprises.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now