Multiple Alter Column statments...
-
Hi is there a way I can alter multiple columns in a single alter table query..?
I.e.
Alter table Test
Alter Column Col_1 datetime
go
Alter table Test
Alter Column Col_2 nvarchar
etc..
As you can see this is very long, is there a simplier way for multiple columns?
Thanks
-
Don't think so.. Couldn't get any 'shorthand' to work anyway...
OTOH, I don't see that the 'correct' syntax should look so overwhelmingly long...?
alter table test alter column col1 datetime null
alter table test alter column col2 varchar(20) null
....
It's kinda clear and to the point
/Kenneth
-
True, bad habits die hard i guess!
Cheers for that
-
T things that u hv blogged above r right.But may i know t tech reason for why Microsoft restricts altering more than one column in a statement .
i.e., alter table test alter column col1 datetime null, col2 varchar(20) null ....
Regret if it is being silly
-
I wonder why MS does not restrict altering more than one column within one month.
_____________
Code for TallyGenerator -
To give way for version 10.0. Hahaha!
-
I assume you have data in the table. Every time you alter a column in a table, SQL Server will copy all the data in the old format to the new format. If you alter 2, 3 columns, it will copy the data 2, 3 times and it will take a long especially if you have a big table.
I did this before and that was what I did. I created a table with the new format, then copied the data from the old table to the new table, deleted the old table and renamed the new table to the old table name. It was much faster.
CREATE TABLE NewTable (COL1 NVARCHAR(10),
COL2 NVARCHAR(20)....)
INSERT INTO NewTable (COL1, COL2...)
SELELCT COL1, COL2 ....
FROM TableA
DROP TABLE TableA
EXEC sp_rename 'NewTable', 'TableA'
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply