I have a table which consist of 7 million rows & 110 columns with data type set to nvarchar (originally taken imported using SSIS + flat file) in SQL Server 2005. My goal is to give the proper data types to each column, however I am running into problems when trying to convert/cast to numeric format.
I figured out that the fact that the data is currently in nvarchar was giving me problems, so I did a 'Create Table' (set the data types accordingly one 3x varchar & rest numeric(15, 8)), then INSERT INTO > SELECT cast( column AS numeric (15,8))...etc.
Received errors such as 'Arithmetic overflow error converting varchar to data type numeric'.
I did a search(using select) for TABS, '*', '(' or any other charchers and could not find anything in this one column which i isolated out of the 110. Also not this particular column has only whole number currently, however I would think '0' would be patted for now.
I understand that using the numeric (e.g 'numeric (15,8)' ) data type that 15 represents the total amount of numbers in my recordset and the 8 (part of the 15) would be my precision (making the largest number to be 9999999.99999999).
Questions: What am I doing wrong? Is there another method for casting correctly? Why does MS have to make something so easy so complicated.