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
Change data types in Design view If you do not have the table open, in the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu. Locate the field that you want to change, and select a new data type from the list in the Data Type column. Save your changes.
As long as the data types are somewhat "related" - yes, you can absolutely do this. You can change an INT to a BIGINT - the value range of the second type is larger, so you're not in danger of "losing" any data.
You can modify the data type of a column in SQL Server by using SQL Server Management Studio or Transact-SQL. Modifying the data type of a column that already contains data can result in the permanent loss of data when the existing data is converted to the new type.
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.
Something Like
Alter Table [MyTable] Add Column 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With