| Author |
Topic |
|
dewacorp.alliances
452 Posts |
 Posted - 2009-06-02 : 20:52:45
|
| Is it possible to add a column to a table using the "alter table"statement and specify where in the sequence of columns the new columnsits. If not is there any way to alter the order of columns using TSQLrather than Enterprise Manager / Design Table. The reason I ask this due to I have a large data in this table already it said timeout:"Unable to modify table. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."What is the other way to do this? Thanks |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2009-06-02 : 23:26:33
|
| No - but you may be able to write it to run faster than the Enterprise Manager/Management Studio table designer.What you need to do is 1. use a CREATE TABLE statement to create the table with the columns the way you want2. run an INSERT ... SELECT to insert the rows from the old table to the new table3. drop the old table4. run sp_rename to rename the new table to the old table nameYou'll need to drop any constraints that reference the table and then add them back. |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2009-06-02 : 23:30:26
|
why the ordering of the column is important to you ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-06-03 : 01:55:54
|
| In your application, if you refer the columns by name instead of index, you wont have problemsie Use rs("colname") instead of rs(0)MadhivananFailing to plan is Planning to fail |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-03 : 02:53:34
|
The only reason for this to be a problem is that if you are using "SELECT *" in a production environment.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2009-06-03 : 06:01:57
|
| The reason is because we have a column that already have GrossNetExGST, GrosNetIncGST. We would like to add GrossGST which it's at the moment right at back on that table. I guess it's a matter of cosmetics thing. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-03 : 06:05:27
|
Do the end-users have direct access to the tables?
E 12°55'05.63"N 56°04'39.26" |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2009-06-03 : 06:32:23
|
| or you can create a view that has columns in the preferred orderMadhivananFailing to plan is Planning to fail |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-12 : 08:48:56
|
| If you make the change in Table Designer (you can just drag the column into the desired place) and then use GENERATE SCRIPT then SQL server will generate a script for you to make the change [in the way that snSQL said, but including all indexes, constraints, foreign keys, yada-yada] - or you can just press SAVE in Table Designer and it will run the script and make the changes.Might break goodness-knows-what though - e.g.INSERT INTO MyTable -- NOTE: NO list of columns :(VALUES(a, b, c)"We would like to add GrossGST which it's at the moment right at back on that table"When you add it (probably a bit late now!) use Table Designer again and INSERT a blank column row in teh Designer where you want the new column, then put the column details there.Again, it will break INSERTS etc. as per above (maybe you never intent to leave off the column list, but there won't be anything to tell you you've made that mistake until someone runs the program after you make the change Adding the columns at the end will not break in the above example [if they new column(s) have defaults] |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-01-12 : 20:37:14
|
| I think if you re-read Peso's first post you'll see that you can specify the column order when you SELECT the data. Instead of saying "SELECT *", you can say "SELECT GrossNetExGST, GrosNetIncGST, GrossGST, blah, blah" and have the columns exactly as you want them. Also, it is a waste of network bandwidth to be sending all of the columns back from a SELECT statement unless you use them all. So you'll gain a perfromance boost, also.Long answer, short: Don't use SELECT *=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
|
Alter table and column order