how to change datatype of columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to change datatype of columns

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-11-01 : 22:02:20

Hi,
I have this table. I want to change all the columns which are 'int' to 'decimal (9,3)'.
Is there anyway i can do it by sql code ?
This is my table:
CREATE TABLE [dbo].[WeatherActual](
[ID] [int] IDENTITY(1,1) NOT NULL,
[WeatherStationID] [int] NULL,
[DT] [datetime] NULL,
[UpdateDT] [datetime] NULL,
[TemperatureF_1] [int] NULL,
[TemperatureF_2] [int] NULL,
[TemperatureF_3] [int] NULL,
[TemperatureF_4] [int] NULL,
[TemperatureF_5] [int] NULL,
[TemperatureF_6] [int] NULL,
[TemperatureF_7] [int] NULL,
[TemperatureF_8] [int] NULL,
[TemperatureF_9] [int] NULL,
[TemperatureF_10] [int] NULL,
[TemperatureF_11] [int] NULL,
[TemperatureF_12] [int] NULL,
[TemperatureF_13] [int] NULL,
[TemperatureF_14] [int] NULL,
[TemperatureF_15] [int] NULL,
[TemperatureF_16] [int] NULL,
[TemperatureF_17] [int] NULL,
[TemperatureF_18] [int] NULL,
[TemperatureF_19] [int] NULL,
[TemperatureF_20] [int] NULL,
[TemperatureF_21] [int] NULL,
[TemperatureF_22] [int] NULL,
[TemperatureF_23] [int] NULL,
[TemperatureF_24] [int] NULL,
[DewPointF_1] [int] NULL,
[DewPointF_2] [int] NULL,
[DewPointF_3] [int] NULL,
[DewPointF_4] [int] NULL,
[DewPointF_5] [int] NULL,
[DewPointF_6] [int] NULL,
[DewPointF_7] [int] NULL,
[DewPointF_8] [int] NULL,
[DewPointF_9] [int] NULL,
[DewPointF_10] [int] NULL,
[DewPointF_11] [int] NULL,
[DewPointF_12] [int] NULL,
[DewPointF_13] [int] NULL,
[DewPointF_14] [int] NULL,
[DewPointF_15] [int] NULL,
[DewPointF_16] [int] NULL,
[DewPointF_17] [int] NULL,
[DewPointF_18] [int] NULL,
[DewPointF_19] [int] NULL,
[DewPointF_20] [int] NULL,
[DewPointF_21] [int] NULL,
[DewPointF_22] [int] NULL,
[DewPointF_23] [int] NULL,
[DewPointF_24] [int] NULL,
[CloudCoverOkta_1] [int] NULL,
[CloudCoverOkta_2] [int] NULL,
[CloudCoverOkta_3] [int] NULL,
[CloudCoverOkta_4] [int] NULL,
[CloudCoverOkta_5] [int] NULL,
[CloudCoverOkta_6] [int] NULL,
[CloudCoverOkta_7] [int] NULL,
[CloudCoverOkta_8] [int] NULL,
[CloudCoverOkta_9] [int] NULL,
[CloudCoverOkta_10] [int] NULL,
[CloudCoverOkta_11] [int] NULL,
[CloudCoverOkta_12] [int] NULL,
[CloudCoverOkta_13] [int] NULL,
[CloudCoverOkta_14] [int] NULL,
[CloudCoverOkta_15] [int] NULL,
[CloudCoverOkta_16] [int] NULL,
[CloudCoverOkta_17] [int] NULL,
[CloudCoverOkta_18] [int] NULL,
[CloudCoverOkta_19] [int] NULL,
[CloudCoverOkta_20] [int] NULL,
[CloudCoverOkta_21] [int] NULL,
[CloudCoverOkta_22] [int] NULL,
[CloudCoverOkta_23] [int] NULL,
[CloudCoverOkta_24] [int] NULL,
[WindMPH_1] [int] NULL,
[WindMPH_2] [int] NULL,
[WindMPH_3] [int] NULL,
[WindMPH_4] [int] NULL,
[WindMPH_5] [int] NULL,
[WindMPH_6] [int] NULL,
[WindMPH_7] [int] NULL,
[WindMPH_8] [int] NULL,
[WindMPH_9] [int] NULL,
[WindMPH_10] [int] NULL,
[WindMPH_11] [int] NULL,
[WindMPH_12] [int] NULL,
[WindMPH_13] [int] NULL,
[WindMPH_14] [int] NULL,
[WindMPH_15] [int] NULL,
[WindMPH_16] [int] NULL,
[WindMPH_17] [int] NULL,
[WindMPH_18] [int] NULL,
[WindMPH_19] [int] NULL,
[WindMPH_20] [int] NULL,
[WindMPH_21] [int] NULL,
[WindMPH_22] [int] NULL,
[WindMPH_23] [int] NULL,
[WindMPH_24] [int] NULL)
Thanks,
Mavericky

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-11-01 : 22:12:52

copy and paste the result into query window and execute


select	'ALTER TABLE ' + TABLE_NAME + ' ALTER COLUMN ' + COLUMN_NAME + ' DECIMAL(9,3)'
from	INFORMATION_SCHEMA.COLUMNS
where	TABLE_NAME	= 'WeatherActual'
and	DATA_TYPE	= 'int'
and	COLUMN_NAME	not in ('ID', 'WeatherStationID')

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:08:25

why do you need seperate column for each instance like this? Will all these columns be having values always?if not try to make it SPARSE COLUMN set if you're using 2008 or above

Also it might be worth storing data as rows instead as it will not put a max limit on number of values you can store for each type (Temperature,DewPoint,...) and also you can apply crosstabing to get them to columns if you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

mavericky
Posting Yak Master

117 Posts

Posted - 2011-11-06 : 15:58:46

Thanks a lot Khtan and visakh16!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 03:58:33

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources