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 |
|
magictech
Starting Member
44 Posts |
 Posted - 2004-09-18 : 10:31:17
|
| Each time I add a new column to a table using T-SQL, the column always get added at the end of the table (the last column). I want to add a new column in the middle or at the begining of a table. Does anyone knows how this can be accomplish using T-SQL? Thanks in advance |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-09-18 : 18:08:51
|
| Drop the table and recreate it ?Apart from human readability there is no sense to a specific ordering of columns, you define the order the columns returned in your query.Graham |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-09-18 : 19:31:40
|
| Seehttp://www.nigelrivett.net/AlterTableProblems.htmlI would advise against adding columns.You can update syscolumns - even more not advisedYou could also create a view with the columns in the order you wish.==========================================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. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-09-19 : 01:57:36
|
| Use Enterprise Manager "Table Design"Drag or Insert the columns in the order you want.Press the "Medieval Scroll" looking Icon! and it will genrate a script for you(Preferably do NOT press the SAVE icon, which will make the changes - I assume you want to use a script so that you have a proper archive of database changes, AND you have a script to test on Deve first and then roll out to Production)If you insert a new column in the middle of the table structure then EM will create a script that, in essence, does:Drop appropriate constraints/foreign keysCreate temporary tableMake primary keyCreate Properties (descriptive names for table/columns etc)Copy data, if any, from original table to new temporary tableDrop old tableRename temp table to original nameReinstate all constraints/foreign keysThis would clearly be a lot of messing around writing it by hand!However, beware that this route does NOT generate COLLATE statemetns for varchar/text (which scripting the database from "Generate SQL _does_ do) [this only applies to columns using a collating sequence which matches the default], so beware if you are likely to run the script on a server with a different default collating sequence.Kristen |
|
|
|
|
|
|
Adding columns in a specific position in a table