September 4, 2008 at 8:30 am
Hi,
A short question : let's say I have a table with the columns: A, B, and D ;and I want to add column C in the third position(A,B,C,D).Can I do this without droping an re-creating(if there is any information in the table,it's more complicated...)? can T-SQL "help" me?
thx
September 4, 2008 at 8:34 am
If you do it in SSMS and insert the column in the position you want it will work, but it DOES drop and re-create the table. To my knowledge, you can't specify column order when adding a column without dropping and re-creating.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2008 at 10:14 am
Since column order in the table is very specifically supposed to NOT matter, the only way to specify it is to drop and create.
Why does the order matter to you in this case?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 4, 2008 at 10:24 am
I may be dumb here. But if you pull up the table in Mangement Studio.. Click Design.. You can then add any column in any position that you would like. Try not to do this on a large table unless you have not processing going on with it.
Does this drop and re-create... And if this is a drop and re-create process then why does the data still stay there.
Plus look at ALTER TABLE
September 4, 2008 at 11:50 am
When you add a column in SSMS in a certain position SSMS it does the following:
2. Creates a new table tmp_tablename with the new column list
3. Inserts the data from the old table to the new table
4. Drops the old table
5. Renames the new table
6. Re-creates constraints and relationships.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 4, 2008 at 1:20 pm
I think you need to add indexes to that rebuild list as well!!!
September 4, 2008 at 1:34 pm
Yes, it does do indexes as well. I guess I was just showing that it is not a simple alter table add column. I found all the steps by running profiler and adding a column and adding one column and tracing only the sp:completed, rpc:completed, and SQL:BatchCompleted events I had over 200 events fire!
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 5, 2008 at 12:12 am
The order matters because I was asked to do this:D.
I just asked because I wanted to know if is was possible without doing all the droping , saving the information and recreating,if there's a easy way.And not from designer,with T-SQL:)...That's all
thx for all the responses
September 5, 2008 at 12:22 am
1 question does column position matters to you / database ?
you can add the column at specific order either by the way mention by Jack Corbett. there is not other way to do it.
September 5, 2008 at 12:31 am
ok
I understand that's the way to do it.
It's not a critical thing,but for example....i like to have the Id in my first column...and maybe to have an order in the columns....and this it wasn't my request...I just received "the order"...and I have to do it:)
September 5, 2008 at 6:31 am
I'd love to hear their reasons for wanting this... then I'd also love to have them read those forums!
September 5, 2008 at 6:42 am
I've said that the process is "difficult" and it's better to add it where it will be asigned...
no reasons received.who's the boss...needs no reasons :).
Anyway...I did it with temp table and drop + create, and finsihed ...and don't want to hear about it again.I hope they will remember this situation next time:)
September 5, 2008 at 8:35 am
My sympathy for you. Sometimes bosses do demand things that don't actually make sense, and it's easier to just go with it than to fight for the right way. In a case like this, re-creating the table in order to re-order the columns shouldn't actually hurt anything (unless someone has insert code that uses implicit columns), so it's probably not worth the fight.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 5, 2008 at 8:46 am
Lol, ya I forgot about that one... hoping that didn't screw up anything with the application. Only time will tell.
That actually might work in your favor if it did :w00t:.
September 5, 2008 at 9:03 am
it's ok...for now:D...
I was careful with this also....
When i did it, i did it...static to say so.
I'm working on a stored procedure to do this dinamic...at least indexes and data.For procedure and the rest..."on hand",or if I have time...I will also try a stored procedure for this,unless somebody already did it :):D.
unfortunately I'm a begginer...and I have a lot to learn...
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply