| Author |
Topic |
|
pnpsql
Posting Yak Master
246 Posts |
 Posted - 2012-02-20 : 23:07:17
|
| hi team , i need have a "customer_details" table with certain columns like : id fname mname lname occupation contact i need to add a column loginname just after lname .how can i do this , when i alter the table the column added at lastplease guide me .challenge everything |
|
|
Azhar Khan
Starting Member
3 Posts |
Posted - 2012-02-21 : 04:12:42
|
| why do you need the loginname column just after lname?Can loginname as the last column will solve your purpose?or if you want this column after lname you can select these columns in select query as per your requirements after the table has been created.For example:-select id,fname,mname,lname,loginname,occupation,contact from customer_detailsAzhar khan |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-21 : 05:50:47
|
| Drop and recreate the table, that's the only way to get columns in a particular position. That said, why do you need to? There's no meaning associated with column position. Want it in a particular place in resultsets? Just write the columns in the select in the order you want.--Gail ShawSQL Server MVP |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-02-21 : 09:20:58
|
quote:
Originally posted by GilaMonster
Drop and recreate the table, that's the only way to get columns in a particular position. That said, why do you need to? There's no meaning associated with column position. Want it in a particular place in resultsets? Just write the columns in the select in the order you want.--Gail ShawSQL Server MVP
But... when i do SELECT * FROM it is not in the right order!! (cry). http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Jan Novak
Starting Member
6 Posts |
Posted - 2012-02-21 : 10:12:37
|
| You can change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse. You can save these changes only when the option "Prevent saving changes that require table re-creation" is unchecked in Options -> Designers. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2012-02-21 : 10:26:44
|
quote:
Originally posted by Jan Novak
You can change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse. You can save these changes only when the option "Prevent saving changes that require table re-creation" is unchecked in Options -> Designers.
And if you script out what SSMS is doing, it will be exactly what Gail said to do.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2012-02-21 : 10:36:33
|
quote:
Originally posted by Jan Novak
You can change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse. You can save these changes only when the option "Prevent saving changes that require table re-creation" is unchecked in Options -> Designers.
Do that on a several million row table and you'll be waiting hours and possibly filling the transaction log too. That recreates the entire table.btw, the whole reason that setting is there is to prevent people from accidentally doing just that on a production server--Gail ShawSQL Server MVP |
|
|
|
Jan Novak
Starting Member
6 Posts |
Posted - 2012-02-21 : 10:54:30
|
quote:
Originally posted by DonAtWork
quote:
Originally posted by Jan Novak
You can change the order of the columns in SSMS if you go into the table design view and just select a column and move it up or down with the mouse. You can save these changes only when the option "Prevent saving changes that require table re-creation" is unchecked in Options -> Designers.
And if you script out what SSMS is doing, it will be exactly what Gail said to do.http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp
Yes of course. I wrote OP must uncheck "Prevent saving changes that require table re-creation", so the table is internally recreated. |
|
|
|
|
alter table add column