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 NVARCHAR I need to change its type to decimal(18, 2) wit

Solution 1:

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

ALTERTABLE table_name ADD new_column_name decimal(18,2)

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

ALTERTABLE table_name DROPCOLUMN old_column_name

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

Solution 2:

Something Like

AlterTable [MyTable] AddColumn NewPrice decimal(18,2) nullThenUpdate [MyTable] Set NewPrice =Convert(decimal(18,2),[Price]) Where Price isnotnull

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

Solution 3:

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

AlterTable YourTableName
AlterColumn ColumnName DataType 

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

Solution 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..