Change column type without losing data

Question

I am working on an SQL Database, I have a column named "Price". When the database was created the column "Price" was set to NVARCHAR 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?

Thanks

Mahau

Have you done some research on how to do this? What have you got and where are you stuck? – Iceblink

Answer 1

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.

Disclamation

Missing step where you rename new_column_name to old_column_name, still +1 – Firestone

I got "Syntax error: unexpected 'decimal (decimal)'" on the convert() function – Flour

Sure wish mysql just did this for you :/ Seems like busy work. Thanks for answer! – Tattan

Answer 2

Something Like

Alter Table [MyTable] Add NewPrice decimal(18,2) null

Then

Update [MyTable] Set NewPrice = Convert(decimal(18,2),[Price]) Where Price is not null

If the above fails then you'll need to beef it up to deal with the funnies

Once you are happy drop the old column with an Alter Table and rename the new one with sp_rename

Paramnesia

Answer 3

If you just want to change only column's data type, you can do like this=>

Alter Table YourTableName
Alter Column ColumnName DataType 

I already test this on SQL server 2012 and its work.

Holm

Answer 4

You can make those changes visually using Management Studio. Then, use the button "Generate Change Script" to get the script for the changes you made. Be sure to do all testing in a copy of the original ddbb, in case something goes wrong..

Ptisan

Note - how to find that : https://mcmap.net/q/500607/-generate-changes-script – Avoidance