Sql-server – How to modify a column type if the DB has many values

sql server

I'm currently trying to convert a column in SQL Server from nvarchar(255) to decimal(18,0). However, this is the error I get:

'member_details' table
– Unable to modify table.
Error converting data type nvarchar to numeric.

Before this error, Management Studio gave a warning about changing the datatype of columns with many rows. My DB contains about 1,000,000 rows.

Can anybody help? Any help will be greatly appreciated.

Best Answer

The problem isn't the number of rows, the problem is that you have a row which does not fit into the decimal(18, 0) data type. If you're on SQL 2012 you can identify the rows fairly easily (and can do your own number checking function on other versions).

Create Table dbo.Moo (
    Data Nvarchar(255)   
)

Insert dbo.Moo Values ('-1'), ('100'), ('1.1'), ('Cows')

Select  Convert(Decimal(18, 0), Data)
From    dbo.Moo
-- Msg 8114, Level 16, State 5, Line 7
-- Error converting data type nvarchar to numeric.

Select  *
From    dbo.Moo
Where   Data Is Not Null
And     Try_Convert(Decimal(18, 0), Data) Is Null
-- Cows

Once they're identified you can fix the data on those rows and then convert the table over as you originally attempted.

Related Question