CODEWITHSUNDEEP
sqlsql-serversql-server-2008type-conversion
MoonKnight
MoonKnight

Reputation: 23833

SQL Server Conversion Failure when using INSERT

I have an existing table full of data, which can be created using 

CREATE TABLE __EpisodeCost
(
    ActivityRecordID INT NOT NULL, 
    ActCstID NVARCHAR(15), 
    VolAmt FLOAT, 
    ActCnt FLOAT, 
    TotCst FLOAT, 
    ResCstID NVARCHAR(50)
);

This comes from a feed I have no control over and I want to convert this to my own version called EpisodeCost

CREATE TABLE EpisodeCostCtp
(
    ActivityRecordID INT NOT NULL, 
    ActCstID NVARCHAR(6), 
    ResCstID NVARCHAR(7), 
    ActCnt NVARCHAR(7), 
    TotCst DECIMAL(18, 8) 
);

Now, the problem I am having is with conversions. I can execute the query 

SELECT 
    ActivityRecordID, 
    Cast(ActCstID AS NVARCHAR(6)), 
    Cast(ResCstID AS NVARCHAR(7)), 
    Cast(LTRIM(STR(ActCnt, 10)) AS NVARCHAR(7)), 
    Cast(TotCst AS DECIMAL(18, 8)) 
FROM __EpisodeCostCtp;

and it provides data, however, when I try to execute 

INSERT INTO EpisodeCostCtp 
    (
        ActivityRecordID, 
        ActCstID, 
        ResCstID, 
        ActCnt, 
        TotCst 
    ) 
SELECT 
    ActivityRecordID, 
    Cast(ActCstID AS NVARCHAR(6)), 
    Cast(ResCstID AS NVARCHAR(7)), 
    Cast(LTRIM(STR(ActCnt, 10)) AS NVARCHAR(7)), 
    Cast(TotCst AS DECIMAL(18, 8)) 
FROM __EpisodeCostCtp;

I get

Msg 8115, Level 16, State 8, Line 102 Arithmetic overflow error converting numeric to data type numeric. The statement has been terminated.

Why can I SELECT using the relevant casts, but then cannot INSERT into the target table?

Edit. I still don;t fully know what is occurring here.

As per Serg's recommendations, I have attempted to locate the problematic records but the query 

SELECT 
    ActivityRecordID, 
    Cast(ActCstID AS NVARCHAR(6)), 
    Cast(ResCstID AS NVARCHAR(7)), 
    Cast(LTRIM(STR(ActCnt, 10)) AS NVARCHAR(7)), 
    Cast(TotCst AS DECIMAL(18, 8))      
FROM __EpisodeCostCtp
WHERE TotCst > 9.999999999999999e9;

returns zero records. Changing to 9.999999999999999e8 does, and the conversion/cast happens with out error. I have scince changed the INSERT query to use DECIMAL(36, 18) and now the insert succeeds, but I am still none the wiser. Clearly I was hitting a limit on the cast, but why SELECT works and INSERT fails, I still don't know.

Upvotes: 4

Views: 1320

Answers (3)

Jayvee
Jayvee

Reputation: 10875

as is an overflow issue you might want to try

SET ARITHABORT OFF

from https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql

When an INSERT statement encounters an arithmetic error (overflow, divide by zero, or a domain error) occurring during expression evaluation, the Database Engine handles these errors as if SET ARITHABORT is set to ON. The batch is stopped, and an error message is returned. During expression evaluation when SET ARITHABORT and SET ANSI_WARNINGS are OFF, if an INSERT, DELETE or UPDATE statement encounters an arithmetic error, overflow, divide-by-zero, or a domain error, SQL Server inserts or updates a NULL value. If the target column is not nullable, the insert or update action fails and the user receives an error.

Upvotes: 3

Serg
Serg

Reputation: 22811

To find rows which cause the problem try

SELECT *       
FROM __EpisodeCostCtp
WHERE TotCst > 9.999999999999999e9;

Upvotes: 0

Coskun Ozogul
Coskun Ozogul

Reputation: 2469

I believe that in your TotCst column has values bigger than DECIMAL(18,8). And the version 2008 of SQL SERVER gets a rounded result if the destination type is not large enough. But it can't insert because the table accepts exact values only.

You can see the result of my two queries on SQL SERVER 2012 :

 DECLARE @float FLOAT 
 SET @float = 12345678911.12
 SELECT CAST(@float AS decimal(18,8))

Result : An arithmetic overflow error occurred when converting float to numeric data type.

the same query with DECIMAL(19,8) instead :

 DECLARE @float FLOAT 
 SET @float = 12345678911.12
 SELECT CAST(@float AS decimal(19,8))

Result : 12345678911.12000100

EDIT : Check if your query tool fetchs all records. If not, maybe among the rest of records there are some values bigger than DECIMAL(18.8)

Upvotes: 0

Related Questions