how to insert columns between existing columns of data?

Posted on 2011-05-03
Last Modified: 2012-05-11
How to insert columns between existing columns of data?
i.e. column 1 - 30 have existing data, but the data supplier has changed their format to house 5 columns between the existing columns 24 and 25
Question by:kgittinger
    LVL 33

    Expert Comment

    The order of the columns shouldn't matter at all, unless you're querying by column number rather than name.

    That said, when you Design the database to add the columns, you can drag and drop the column(s) in whatever order you like.
    LVL 16

    Expert Comment

    by:Imran Javed Zia
    LVL 10

    Expert Comment

    by:Asim Nazir
    See the image:
     Insert column
    LVL 22

    Accepted Solution

    You should be aware that when you try to make the changes that you are talking about (even if you add the columns at the end and try to drag and drop them to new positions), the amount of data in your table may preclude your ability to successfully complete those changes.  In order to insert the columns into the middle of the table, Management Studio will have to create a new table with the columns inserted in the middle of the other columns, copy the data from the original table to the new table, drop the existing table, and then rename the new table to the same name as the old table.  If you have data diagrams or PK/FK constaints, you may not be able to accomplish this through MS and, instead, you may have to script the whole process yourself (including the re-establishment of the PK/FK relationships) after dropping the existing table out of any diagrams it is in.  Inother words, this is potentially a royal pain in the, uh, elbow.

    If possible, it would be a lot easier to deal with by adding the columns at the end of the table and then handling the "relocation" of the data during the process of importing the source data and, if necessary, during any querying of the data.

    Also, with regard to the comment:

    The order of the columns shouldn't matter at all, unless you're querying by column number rather than name.

    If you are querying by column number, then you need to break that habit!  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

    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