Alter Table Syntax (Column Order)

[skuhlman]

I have a table MyTable with column, MyColB, MyColC and MyColD. I want to create a new column called MyColA as an identity column. My ALTER TABLE code works fine for that, but I want the column to be the first column instead of the last column. Here's the code I'm using:

ALTER TABLE MyTable ADD MyColA INTEGER IDENTITY (1,1)

What's the code needed to make MyColA the first column?

 

[nigelrivett]

There isn't one.
You should recreate the table if it worries you.
Another way is to rename it and access it from a view with the table name.

The column order shouldn't make a difference.
You can edit sysocolumns but I wouldn't advise it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[skuhlman]

Thanks. I know the order doesn't really matter. It's more a curtesy to the other developers on the team. I did try the UPDATE SYSCOLUMNS... thing and it gave a message about changing the server settings. I don't really want to do that. I'll go the route of creating a copy and re-building that table.

 

[john76]

Try doing the modification in Enterprise Manager instead of Query Analyzer.

I believe that in the background it actually does recreate the table, but the end result is that the columns are in the order you put them in.

HTH,
John

 

[nigelrivett]

You can generate the script via enterprise manager then run it.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.