[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Arithmetic overflow error converting varchar to data type numeric.

Posted on 2008-06-16
4
Medium Priority
?
3,494 Views
Last Modified: 2012-06-21
Hello,

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.


0
Comment
Question by:RunnerNwizard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 11

Accepted Solution

by:
CMYScott earned 1000 total points
ID: 21799223
there is also CONVERT - however I don't believe it will give you any additional functionality to solve your issue

in the case where there is some value that cannot be converted to numeric, you might want to use the
ISNUMERIC function

you could either use it in your INSERT like this..

INSERT INTO ...... SELECT Cast(column as numeric(15,8)) FROM yourTable
WHERE ISNUMERIC(column) = 1

or, you might want to use it to find values in the table that cannot be converted to numeric and then fix those values..

SELECT * FROM yourTable WHERE ISNUMERIC(column) = 0

keep in mind, the ISNUMERIC only tests to see if a value can be cast to one of the numeric types, it does not test to see if the value can be cast to a specific type without causing an overflow, etc.

Good Luck
0
 
LVL 2

Assisted Solution

by:howyue
howyue earned 1000 total points
ID: 21801340
from ur error msg and problems statement i would say it has a very high possibility that the root of the problem is ur raw data, which i believe u already aware... u can use CMYScott method to fix the invalid data before inserting them. btw, inserting 7 millions record using insert into is not efficient, u should try to use bulk insert.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question