March 8, 2008 at 12:45 am
Hi,
I want to change multiple column datatypes in a single query. If any one knows the answer, please mail me the answer to change the multiple columns datatypes at a time.
Thank you in advance.
Sowbhagya
March 8, 2008 at 7:29 am
You woud have to do something like this:
ALTER TABLE
ALTER COLUMN COLUMNNAME DATATYPE
ALTER TABLE
ALTER COLUMN COLUMNNAME DATATYPE
If you do not want to do ths then you will have to drop the table and recreate. You can perform the modification in SSMS and generate a change script. The MS change script is the best way to accomplish your task, as it does all the work for you.
The basic pseduocode for a MS change script is drop constraints, create temp table, insert all table data into temp table, drop original table, use sp_rename to rename the temp table to the original table name, add constraints back.
March 9, 2008 at 10:53 pm
thanks. But this query works only to modify one column. I need the query to modify more than one column at a time. Please send me the query to do so.
thank you once again.
Sowbhagya
March 9, 2008 at 11:09 pm
swlakshmi (3/9/2008)
thanks. But this query works only to modify one column. I need the query to modify more than one column at a time. Please send me the query to do so.thank you once again.
Sowbhagya
As far as I know, you cannot do more than one ALTER COLUMN in a single ALTER TABLE statement. You can do multiple ADD's or DROP's, but only one ALTER COLUMN per statement.
Why can't you just do multiple ALTER TABLE..ALTER COLUMN.. statements and wrap a transaction around them?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 11, 2008 at 8:47 pm
thanks. But this query works only to modify one column. I need the query to modify more than one column at a time. Please send me the query to do so.
You have to drop and recreate the table. My previous post explains Microsoft's method which works well, with a little tweaking.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply