Alter table query

How do I arrange the column i create to be in a certain order

at the moment I have query to add a column but the column im adding , i want to put before a certain column, example below'

ALTER TABLE Table1 ADD col D Text

example:

col A, col B , col C, col D

I want to put col D before col B

Output Below:

col A, col D, col B , col C

There is no extension to ALTER TABLE that allows you to specify the ordinal position of a new column (either for adding a new column or moving an existing column).

Many experts have commented on this issue in different threads. And the consensus opinion is that unless you're doing "SELECT * FROM table" type commands, the order of columns shouldn't matter. In production code, you really should use explicit column names in your SELECT statements.
So, if you follow the recommendation for only accessing your fields by name, and not by * or ordinal position, the order of columns in your tables should not matter.

Extra tip: When I was researching this, I noticed that there are differences between Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express. The express version lacks a lot of options found in the full version. For example, attaching a database has more options in the full version than on the express version.
Its not possible in SQLSERVER. If u want to add column in particular ordinal position you have to drop the table and
have to create again the same table with your ordinal choice. Incase if u have data in table, get them into one temporary table and drop the table and recreate the table again. then restore the data from temporary table..........

It is very easy to change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse.

SQL SERVER – Change Order of Column In Database Tables
Hope this helps you.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sam Hobbs16y ago
You are not using SQL Server, correct? You should specify the context of what this is for. Is this for Access? If it is and if this is a one-time change then just use Access to change the Access table.
16y ago
There is no extension to ALTER TABLE that allows you to specify the ordinal position of a new column (either for adding a new column or moving an existing column).

Many experts have commented on this issue in different threads. And the consensus opinion is that unless you're doing "SELECT * FROM table" type commands, the order of columns shouldn't matter. In production code, you really should use explicit column names in your SELECT statements.
So, if you follow the recommendation for only accessing your fields by name, and not by * or ordinal position, the order of columns in your tables should not matter.

Extra tip: When I was researching this, I noticed that there are differences between Microsoft SQL Server Management Studio and Microsoft SQL Server Management Studio Express. The express version lacks a lot of options found in the full version. For example, attaching a database has more options in the full version than on the express version.
Its not possible in SQLSERVER. If u want to add column in particular ordinal position you have to drop the table and
have to create again the same table with your ordinal choice. Incase if u have data in table, get them into one temporary table and drop the table and recreate the table again. then restore the data from temporary table..........

It is very easy to change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse.

SQL SERVER – Change Order of Column In Database Tables
Hope this helps you.