CODEWITHSUNDEEP
sqlsql-server
oneman
oneman

Reputation: 51

How to move a certain SQL column to a different location within the table?

Let's say my table looks like that:

B, C, D, A

How can I move 'A' to the first location?

Upvotes: 3

Views: 2948

Answers (5)

alejandrob
alejandrob

Reputation: 681

The simple answer for OCD fellas like myself: Export the old structure to a new one, created with the desired column order.

Another option (for tables of manageable size), is to use the SSMS Design option. Just drag and drop the columns and place them in the desired order. Can't be easier than that.

P.S. Don't forget to drop and re-create all the indexes before and after the modification!

Upvotes: 0

Ajay Prajapati
Ajay Prajapati

Reputation: 63

I think this may help you.

Change Column Order in a Table in SQL Server

Upvotes: 2

Vinicius Câmara
Vinicius Câmara

Reputation: 45

You can modify column with MODIFY command without losing data.

Check this in:

How to change the column position of MySql table without losing column data?

You can do that:

ALTER TABLE table_name MODIFY password varchar(20) AFTER id

Upvotes: -2

JohnHC
JohnHC

Reputation: 11195

As per my comment, this is cosmetic. Maybe someone needs this done for no reason whatsoever.

So, for the sake of argument, I'll write a thing for you...

select *
into #supertemp
from Mytable

drop table MyTable

create table MyTable (A INT,           -- replace with your datatypes in the correct column order
                      B varchar(30),
                      C datetime,
                      D INT)

insert into MyTable
select A,B,C,D
from #SuperTemp

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520888

I don't think that columns in a SQL table necessarily have any internal order. Rather, the order in which a column, or derivation of a column, appear in a result set depend on the order which you set when you SELECT.

If you want to change the order of the columns in SQL Server Management Studio, then follow the documentation.

Upvotes: 8

Related Questions