0

Does anyone know of a way in Microsoft SQL where you can amend the table via script, but keep the order of the table.

For example:

Table1:

:Column6
:Column5
:Column4
:Column3
:Column2
:Column1

Lets say i want to Add in :Column7, But i want the order to still be in 7,6,5,4,3,2,1

I know its possible to drop the table and recreate Or manually go into the designer and add in the column7, when inserting Column7 above 6 it will keep it in order.

I want a efficient way of doing this via a script for upgrading customers, The order is important as when manually analysing the data it makes it easy when its in X order an has been for many year. The designer is perfect for adding in columns but leads to many errors in stuff being forgotten or miss typed.

Curious if anyone else has been in a similar situation and how they have worked around it.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Dave Hamilton
  • 675
  • 1
  • 12
  • 23
  • Anytime you select columns for viewing you can change the order however you want. Trying to change the order in the DB is not a good idea, bad for performance, and can cause entire table to be dropped/recreated (behind the scenes) by doing this. Just select the columns in the order you want. – Brad Mar 15 '21 at 14:52
  • @Brad Understand and this would be a a solution to normal situations, but when working with existing DB that been appended for 10 years and has over 120 columns, its not as easy as one would think. Am trying to eliminate constant mistakes in an already bad situation. but the only thing i have to stick by is the order the columns are presented in. – Dave Hamilton Mar 15 '21 at 15:21
  • The short answer is - you can't. To change the order of columns in a table requires you to do exactly what you already know - re-create the table in some fashion. To do that means you must deal with the dependencies on that table. It is complex scripting and it will NOT be efficient. – SMor Mar 15 '21 at 16:19
  • Please explain better why the column order actually matters – Charlieface Mar 15 '21 at 16:19

2 Answers2

1

How about just adding the column and then using a view to get the columns in the order you want?

alter table t add column7 . . .;

create view v_t as
    select column7, column6, column5, column4, column3, column2, column1
    from t;

This is even an updatable view, so you can insert columns into it without specifying columns (which I oppose) or use bulk insert.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

Column ordering shouldnt really be a concern in MSSQL unless you are using the column ordering for a very specific use case. You can always fix column order if it is for something like 'insert' purposes within an insert or select statement. For reference I believe this question has been asked previously and here is the link to the original stack over flow question, I would scroll through some of the answers here.

How to change column order in a table using sql query in sql server 2005?

Helana Nosrat
  • 11
  • 3