Swap column position

[Doug Baroter]

What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
email 5
city 6
state 7
....

Now, I need to make city as 2 and state as 3. BTW, the tblXYZ table
has data there. Copy/select all the data into a new table with
desired column position would require constraints re-mapping etc.,
which seems quite a bit hassle.

Thanks.

[Greg D. Moore \(Strider\)]

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
email 5
city 6
state 7
...

Now, I need to make city as 2 and state as 3. BTW, the tblXYZ table
has data there. Copy/select all the data into a new table with
desired column position would require constraints re-mapping etc.,
which seems quite a bit hassle.
I'm not sure what you mean. It might help if you can explain why the
physical order of the columns is relevant. (hint, generally it should not
be.)

I mean normally you'd simply do:

select xyzUUID, city, state, fn, ln, phone, email from tblXZY


Thanks.

[Steve Kass]

Doug,

Ideally, very little should depend on the order in which
the columns are arranged, and every query should contain
a column list so that this won't matter.

The only thing I can think of that does depend on it offhand is
the COLUMNS_UPDATED() bitmask available to triggers,
and unfortunately, I've seen bugs where the column id values
on which it depends can end up with gaps.

I think you can do this in the Enterprise Manager design GUI,
by selecting, then dragging, the column definitions. Behind
the scenes it will copy all the data into a new table, I believe.

-- Steve Kass
-- Drew University
-- Ref: D64B08D4-9E4A-4F29-8847-5E9AAA89B900


Doug Baroter wrote:

What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
email 5
city 6
state 7
...

Now, I need to make city as 2 and state as 3. BTW, the tblXYZ table
has data there. Copy/select all the data into a new table with
desired column position would require constraints re-mapping etc.,
which seems quite a bit hassle.

Thanks.

[Erland Sommarskog]

Steve Kass (sk***@drew.edu) writes:

Ideally, very little should depend on the order in which
the columns are arranged, and every query should contain
a column list so that this won't matter.

When I add new columns to tables in our database, I very rarely put them
last, but I put them where they fit in logically. Say that I'm working
with Northwind..Customers and there is a requirement to add a column
Address2, because some companies have complex addresses. I would add
this column between Address and City, not after Fax.

This has nothing to do with how the code works in the database, but is for
the benefit for the people who work with the data model.

And while SELECT * is a no-no in code, I often use it debug situations.
When you have some 70 columns, it helps if you know where a column is,
and it certainly helps if two related columns are close to each other.

It is also an advantage if you have more than instance of the same database
that the column order is the same both databases, in case you need to
move data with bulk copy.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[John Bell]

Hi

To do this you will have to look at creating a new table with the columns in
the order you require (you may want to use the "create SQL script" option to
do this)

Then INSERT the data using INSERT INT NewTable ( Col1... ) SELECT Col1,....
FROM OldTable.

Then drop the old table, and rename (using sp_rename) the new table.

This is basically what the code behind enterprise manager will do if you
force it by changing one of the existing columns in some way (e.g. change of
datatype). EM will just create a script that adds the columns onto the end
if you don't force it to re-create the table. To see the script generated by
the table designer choose the button with the scroll on it.

HTH

John

"Doug Baroter" <qw********@boxfrog.com> wrote in message
news:fc**************************@posting.google.c om...

What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
email 5
city 6
state 7
...

Now, I need to make city as 2 and state as 3. BTW, the tblXYZ table
has data there. Copy/select all the data into a new table with
desired column position would require constraints re-mapping etc.,
which seems quite a bit hassle.

Thanks.

[Doug Baroter]

Steve Kass <sk***@drew.edu> wrote in message news:<bg**********@slb2.atl.mindspring.net>...

Doug,

Ideally, very little should depend on the order in which
the columns are arranged, and every query should contain
a column list so that this won't matter. Agree 100%.
The only thing I can think of that does depend on it offhand is
the COLUMNS_UPDATED() bitmask available to triggers,
and unfortunately, I've seen bugs where the column id values
on which it depends can end up with gaps. Can't help any help (further information) on COLUMNS_UPDATED()
function from BOL.
I think you can do this in the Enterprise Manager design GUI,
by selecting, then dragging, the column definitions. Behind
the scenes it will copy all the data into a new table, I believe. This is what I was looking for. I should have tried it first myself.
Don't know why sometime I hesititate to experiment nowadays.

Thanks.

Li, Chunshen (Don)
P.S. "Doug Baroter" is not my name, was being mischievious, and not
Tom Sawyer :)
-- Steve Kass
-- Drew University
-- Ref: D64B08D4-9E4A-4F29-8847-5E9AAA89B900


Doug Baroter wrote:

What is a good method/mechanism to swap the position of multiple
columns?
For instance, tblXZY has the followings columns and respective
positions:
tblXZY
======
xyzUUID 1
fn 2
ln 3
phone 4
email 5
city 6
state 7
...

Now, I need to make city as 2 and state as 3. BTW, the tblXYZ table
has data there. Copy/select all the data into a new table with
desired column position would require constraints re-mapping etc.,
which seems quite a bit hassle.

Thanks.

[Doug Baroter]

Thanks anyway. I should have said the rationale for column position
swap is that they've already been defined, however, as you pointed
out, columns should be logically positioned. So, when they are not,
they need swap.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Steve Kass (sk***@drew.edu) writes:

Ideally, very little should depend on the order in which
the columns are arranged, and every query should contain
a column list so that this won't matter.

When I add new columns to tables in our database, I very rarely put them
last, but I put them where they fit in logically. Say that I'm working
with Northwind..Customers and there is a requirement to add a column
Address2, because some companies have complex addresses. I would add
this column between Address and City, not after Fax.

This has nothing to do with how the code works in the database, but is for
the benefit for the people who work with the data model.

And while SELECT * is a no-no in code, I often use it debug situations.
When you have some 70 columns, it helps if you know where a column is,
and it certainly helps if two related columns are close to each other.

It is also an advantage if you have more than instance of the same database
that the column order is the same both databases, in case you need to
move data with bulk copy.