September 13, 2011 at 9:49 am
Hi,
I have a table with below structure:
Fname Lname DOB City Country Age
I want to swich the column position and want it like:
Fname Lname DOB Age City Country
I tried it while adding the age column to table with below statement but it didn't work:
alter table tname add age int(5) NULL after DOB;
Please help on this..
Thanks in advance!!
Nidhi
September 13, 2011 at 10:53 am
You can't do that quite like that. You have a couple of choices. You can either live with the location of the column, which as far as sql is concerned it makes no difference. The other option is use SSMS to add the column in the position you want it in. This will copy the data to another table, drop the original table, rename the new table behind the scenes. It can be REALLY slow on large tables so be careful if this is a production server. If you are curious about the sql to do this, make your changes in SSMS and then view the change script.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2011 at 12:00 pm
If this is for queries, then you can do this as well:
select
Fname
. Lname
, DOB
, Age
, City
, Country
from Mytable
If its' for something like report builder, you can make this a view. Is there a reason you want Age moved?
September 13, 2011 at 2:22 pm
This is not for queries.. It is related to table design...
Thanks
September 13, 2011 at 3:00 pm
CELKO (9/13/2011)
You are confusing SQL with punch card data processing. Magnetic tapes and traditional sequential files have fields in a record of a file that are ordered.
I would think that if someone was never exposed to punch cards, Magnetic tapes, sequential files, etc it would be hard for them to confuse it with SQL. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 13, 2011 at 3:33 pm
Nidhi G (9/13/2011)
This is not for queries.. It is related to table design...Thanks
If so, what is the issue? SQL doesn't care where you have these columns stored. It isn't helping you with IO or anything else.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply