I am trying to insert data from one table to another table, but we are facing issue it gives us error of
我试图将数据从一个表插入另一个表，但我们面临的问题是它给我们错误
Numeric value out of range: 8115 [Microsoft][ODBC Driver 11 for SQL Server][SQL Server ]Arithmetic overflow error converting varchar to data type numeric. (SQLExecDirect[8115] at /builddir /build/BUILD/php-5.6.30/ext/pdo_odbc/odbc_driver.c:247)数值超出范围：8115 [Microsoft] [SQL Server的ODBC驱动程序11] [SQL Server]将varchar转换为数据类型numeric的算术溢出错误。 （SQLExecDirect [8115] at / builddir /build/BUILD/php-5.6.30/ext/pdo_odbc/odbc_driver.c:247）
We are using below query to insert data
我们使用以下查询来插入数据
`INSERT INTO template_150 ([ClientName],[LOB],[PharmacyTotalClaimAmt])
SELECT PharmacyID,ProductIDNDC
, REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(decimal(10,2),CONVERT(varchar(10),CONVERT(varchar(10),LEFT
(SUBSTRING(REPLACE(TRIM(IngredCost),'\',''), PATINDEX('%[0-9.-]%',REPLACE(TRIM(IngredCost),'\','')),
8000),PATINDEX('%[^0-9.-]%', SUBSTRING(REPLACE(TRIM(IngredCost),'\',''), PATINDEX('%[0-9.-]%',REPLACE
(TRIM(IngredCost),'\','')), 8000) + 'X') -1)) )
),'"',''),'$',''),',',''),'-','') AS [TRIM(IngredCost)]
FROM file_5979bd211a3a9`
I found some solution to use lenth WHERE LEN(IngredCost )<=13 function in where condition, but if we will use this function then we will not able to insert all the records for the table file_5979bd211a3a9, we want to save all the records of table file_5979bd211a3a9, can anyone please give us solutio, how can we resolve this error ?
我找到了一些解决方案，在where where条件下使用lenth WHERE LEN（IngredCost）<= 13函数，但是如果我们将使用这个函数那么我们将无法为表file_5979bd211a3a9插入所有记录，我们要保存所有记录表file_5979bd211a3a9，有谁能请给我们解决方案，我们如何解决这个错误？
1 个解决方案
#1
0
You're converting a 10 character number to a DECIMAL(10,2). That means up to 8 digits in the whole portion and 2 in the fractional. If there are more than 8 numbers in the whole portion of the number, you can't convert that to a DECIMAL(10,2).
您将10个字符的数字转换为DECIMAL（10,2）。这意味着整个部分最多8位数，小数位数2。如果数字的整个部分中有超过8个数字，则无法将其转换为DECIMAL（10,2）。
For example:
例如：
select convert(decimal(10,2),'1000000000')
Try DECIMAL(12,2) or use a VARCHAR(8).
尝试DECIMAL（12,2）或使用VARCHAR（8）。
#1
0
You're converting a 10 character number to a DECIMAL(10,2). That means up to 8 digits in the whole portion and 2 in the fractional. If there are more than 8 numbers in the whole portion of the number, you can't convert that to a DECIMAL(10,2).
您将10个字符的数字转换为DECIMAL（10,2）。这意味着整个部分最多8位数，小数位数2。如果数字的整个部分中有超过8个数字，则无法将其转换为DECIMAL（10,2）。
For example:
例如：
select convert(decimal(10,2),'1000000000')
Try DECIMAL(12,2) or use a VARCHAR(8).
尝试DECIMAL（12,2）或使用VARCHAR（8）。