• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

how to insert columns between existing columns of data?

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
1 Solution
Paul MacDonaldDirector, Information SystemsCommented:
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.
Imran Javed ZiaConsultant Software Engineer - .NET ArchitectCommented:
Asim NazirCommented:
See the image:
 Insert column
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!  
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now