SQL Server Conversion Failure when using INSERT

Question

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.

SQL Server Conversion Failure when using INSERT

3 Answers