SQL插入报错：varchar值'JC one Date'转换为int类型失败求助

尝试向新表插入数据，新增列需要把数值1转换为'JC one Date'、2转换为'JC two Date'……直到10转换为'JC ten Date'。单独执行SELECT查询正常，但执行INSERT时出现错误：

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'JC one Date' to data type int.

仅保留第一个WHEN子句时执行正常，添加全部WHEN子句则报错，相关SQL代码如下：

INSERT INTO [dbo].[BRIDGE_PrePivot_Output]
    SELECT 
        [Employee Number],
        [Cumulative],
        CASE 
            WHEN [Cumulative] = 1 THEN 'JC one Date'
            WHEN [Cumulative] = 2 THEN 'JC two Date' 
            WHEN [Cumulative] = 3 THEN 'JC three Date' 
            WHEN [Cumulative] = 4 THEN 'JC four Date' 
            WHEN [Cumulative] = 5 THEN 'JC five Date' 
            WHEN [Cumulative] = 6 THEN 'JC six Date' 
            WHEN [Cumulative] = 7 THEN 'JC seven Date' 
            WHEN [Cumulative] = 8 THEN 'JC eight Date' 
            WHEN [Cumulative] = 9 THEN 'JC nine Date' 
            WHEN [Cumulative] = 10 THEN 'JC ten Date' 
        END AS [Which Date],
        [JC Date],
        [JC Occupational Area],
        [JC level of Seniority]
    FROM 
        dbo.[BRIDGE_JOBCHA_CUMUL] 
    GROUP BY 
        [Employee Number],
        CASE 
            WHEN [Cumulative] = 1 THEN 'JC one Date'
            WHEN [Cumulative] = 2 THEN 'JC two Date' 
            WHEN [Cumulative] = 3 THEN 'JC three Date' 
            WHEN [Cumulative] = 4 THEN 'JC four Date' 
            WHEN [Cumulative] = 5 THEN 'JC five Date' 
            WHEN [Cumulative] = 6 THEN 'JC six Date' 
            WHEN [Cumulative] = 7 THEN 'JC seven Date' 
            WHEN [Cumulative] = 8 THEN 'JC eight Date' 
            WHEN [Cumulative] = 9 THEN 'JC nine Date' 
            WHEN [Cumulative] = 10 THEN 'JC ten Date' 
        END AS [Which Date],
        [Cumulative], [JC Date],
        [JC Occupational Area], [JC level of Seniority]
    ORDER BY
        [Employee Number]

报错原因 #

1. 目标表列类型不匹配：你要插入的[Which Date]列在BRIDGE_PrePivot_Output中被定义为INT类型，但CASE表达式返回的是字符串，插入时类型不匹配触发转换错误。
2. CASE隐式类型转换：SQL Server会统一CASE表达式的返回类型。仅保留第一个WHEN时，所有匹配行返回字符串，无问题；但添加全部分支后，若存在Cumulative不在1-10范围内的行，CASE会默认返回INT类型（因为Cumulative是INT），此时字符串会被强制转成INT，直接报错。

解决步骤 #

1. 修改目标表列类型
   把BRIDGE_PrePivot_Output的[Which Date]改为字符串类型，比如VARCHAR(20)：

   ALTER TABLE [dbo].[BRIDGE_PrePivot_Output]
   ALTER COLUMN [Which Date] VARCHAR(20); -- 按需添加NOT NULL或默认值
   

2. 给CASE加ELSE分支，确保返回统一类型
   无论Cumulative值是什么，CASE都返回字符串，避免隐式转换：

   CASE 
       WHEN [Cumulative] = 1 THEN 'JC one Date'
       WHEN [Cumulative] = 2 THEN 'JC two Date' 
       WHEN [Cumulative] = 3 THEN 'JC three Date' 
       WHEN [Cumulative] = 4 THEN 'JC four Date' 
       WHEN [Cumulative] = 5 THEN 'JC five Date' 
       WHEN [Cumulative] = 6 THEN 'JC six Date' 
       WHEN [Cumulative] = 7 THEN 'JC seven Date' 
       WHEN [Cumulative] = 8 THEN 'JC eight Date' 
       WHEN [Cumulative] = 9 THEN 'JC nine Date' 
       WHEN [Cumulative] = 10 THEN 'JC ten Date' 
       ELSE 'Unknown' -- 或你需要的默认字符串，比如空字符串''
   END AS [Which Date]
   

3. 优化GROUP BY（可选）
   由于[Which Date]完全由Cumulative推导而来，GROUP BY无需重复写整个CASE，直接用Cumulative即可，代码更简洁：

   GROUP BY 
       [Employee Number],
       [Cumulative],
       [JC Date],
       [JC Occupational Area],
       [JC level of Seniority]
   

内容的提问来源于stack exchange，提问作者OneDevilsSt