使用 INSERT 时 SQL Server 转换失败

Question

我有一个充满数据的现有表，可以使用

创建该表

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

这来 self 无法控制的提要，我想将其转换为我自己的版本，名为 EpisodeCost

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

现在，我遇到的问题是转换。我可以执行查询

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;

但是，当我尝试执行时，它提供了数据

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;

我明白

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

为什么我可以使用相关转换来 SELECT，但随后却无法 INSERT 到目标表中？

<小时/>

编辑。我仍然不完全知道这里发生了什么。

根据 Serg 的建议，我尝试找到有问题的记录，但查询

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;

返回零记录。更改为 9.999999999999999e8 即可，并且转换/转换不会发生错误。我已经将 INSERT 查询更改为使用 DECIMAL(36, 18)，现在插入成功了，但我仍然不知道。显然我达到了 Actor 阵容的限制，但为什么 SELECT 有效而 INSERT 失败，我仍然不知道。

Answer 1

这是一个您可能想尝试的溢出问题

SET ARITHABORT OFF

来自 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.