Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DavidD
Yak Posting Veteran
73 Posts |
 Posted - 2002-07-25 : 01:22:17
|
| Hi Guys, Does anyone know how to specify the ordinal position of a column using Transact-SQL?eg. I have a table with 3 columns.Column Ordinal positionUserID 1Name 2Age 3 If I were to add an extra column using the alter table command, it would automatically be placed at the end (ie position 4). I can change this by going into Enterprise Manager -> Design table and drag the field up, but I need a way of doing this in code. Thanks in advanceDavid |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-07-25 : 04:57:14
|
| This is not possible using the alter table syntax. EM actually copies your data into a temp table , drops the original table and then recreates the table with the columns in the specified order and reinserts the data. This is why its not a good idea to do this using EM especially on large tables. This is how you would have to do it in TSQL select your data into a table , drop the original recreate it , reinsert the data and then drop your temporary table (this does not have to be a #temp table it can be a table in your db with a temp name such as authors_temp_123456 etc)HTHJasper Smith |
 |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-07-25 : 08:41:09
|
| You can save a step if you do this:Create new table with desired structure.Insert dataDrop old table (Consider truncating first if large)Rename old table to new table |
|
|
|
|
|
|
Ordinal position