Changing Column Data Type 2

[BParsons]

SQL Server 2000 Standard Edition

We have large table (a few million records) that has been storing a column as an incorrect data type. This column is supposed to be storing interest rates but the data is being stored as an integer. What would be the best way to convert it to the numeric data type? I'm still a noob at this so I'm sorry if I left out any details you might need.

Thanks in advance.

Brad

 

[gmmastros]

First, make a backup.

Open the enterprise manager, drill down to the database, open the table in design mode, and change the data type.

Now, before you run right out and do that, you should realize that several things may break in the process. I'm specifically talking about stored procedures, views, and possibly other things. I strongly recommend that you copy the database, change the copy, and then test EVERYTHING just to make sure all is working properly.

If there are no problems in the copied database, then you should make another backup of the original and then apply the changes.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLBill]

Just run:

ALTER TABLE mytable ALTER COLUMN mycolumn NUMERIC

Of course, don't forget to add the scale and precision to NUMERIC (IE. NUMERIC(5,2)).

And follow George's suggestion about backing up the database first in case your change breaks anything.

-SQLBill

Posting advice: FAQ481-4875

 

[BParsons]

Worked like a charm!