| Author |
Topic |
|
jsiedliski
Yak Posting Veteran
61 Posts |
 Posted - 2003-04-05 : 01:13:12
|
| I need to add a column to an existing table:ALTER TABLEADD COLUMN col_name......Let's say the table currently has 4 columns.I don't want the new column to be at the end - but in the 2nd position.Is it possible to specify within the ADD COLUMN statement that this column shoud be in the 2nd position - Not at the end. Hope this makes sence - I can't figure out if the ADD COLUMN alows you to specify the ordinal position for the new column.Thanks,Jack |
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2003-04-05 : 01:23:45
|
| Just when I thought no one else cared about such little details...I just found a long discussion on this topic. Sorry to waste your time.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23186I guess the answer is T-SQL only support adding a new column to a table to the end :) |
 |
|
|
harshal_in
Aged Yak Warrior
633 Posts |
Posted - 2003-04-05 : 01:57:03
|
| u can do it using EM.Expect the UnExpected |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-04-05 : 08:24:06
|
| No more than you can do it in t-sql.e-m will create a new table with the new structure, copy the data into it drop the old and rename the new.You can do all this in t-sql too.==========================================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. |
|
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2003-04-10 : 20:47:33
|
| Thanks Guys :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-04-10 : 20:53:24
|
quote:
u can do it using EM.
Eeks! Yes you can do it in EM, but EM is just running T-sql statements just like the ones that you would run in Query Analyzer. To have a look at what EM does, just take a look at the change script by clicking save change script.Tara |
|
|
|
jsiedliski
Yak Posting Veteran
61 Posts |
Posted - 2003-04-11 : 00:21:15
|
I never knew about the change scripts - thanks for the info - this will really come in handy  |
|
|
|
acollins74
Yak Posting Veteran
82 Posts |
Posted - 2003-06-26 : 14:16:34
|
quote:
No more than you can do it in t-sql. e-m will create a new table with the new structure, copy the data into it drop the old and rename the new. You can do all this in t-sql too.
My question is. What is or where can i find the syntax for renaming a table. Can't seem to find it anywhere.Would this be the best way...SELECT * INTO newtableFROM oldtableDROP TABLE oldtableor is there any (un)documented procedures to do this in which I am not aware... Thanks,Adam |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-26 : 14:35:29
|
| sp_rename "Changes the name of a user-created object (for example, table, column, or user-defined data type) in the current database."ExamplesA. Rename a tableThis example renames the customers table to custs.EXEC sp_rename 'customers', 'custs'B. Rename a columnThis example renames the contact title column in the customers table to title.EXEC sp_rename 'customers.[contact title]', 'title', 'COLUMN'All information taken directly out of SQL Server Books Online.Tara |
|
|
|
|
ALTER TABLE