Youssef Youssef - 2 years ago 87
SQL Question

Change column type without losing data

I am working on an SQL Database, I have a column named "Price". When the database was created the column "Price" was set to

I need to change its type to
decimal(18, 2)
without losing the data in the database. This should be done by an SQL Script

I thought of creating a new column, moving the data to it, remove the old column, and then rename the newly created column.

Can someone help me with an example on how to do this?
Also is there a function in SQL to Parse string to decimal?


aF. aF.
Answer Source

You don't need to add a new column two times, just remove the old one after updating the new one:

ALTER TABLE table_name ADD new_column_name decimal(18,2)

update table_name
set new_column_name = convert(decimal(18,2), old_column_name)

ALTER TABLE table_name DROP COLUMN old_column_name

Note that if the old_column_name is not numeric, the convert may fail.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download