SQL Server插入athlete表报Msg245 varchar转int类型转换失败错误的解决
报错根因
你遇到的Msg 245类型转换报错,完全不需要通过将teamId改为VARCHAR类型解决:
- 数据库范式设计要求athlete表通过存储team表的主键
teamId(整数类型)实现外键关联,直接在athlete表存球队名字符串会引入数据冗余,后续修改球队名时需要同步更新所有关联行,极易出现数据不一致问题。 - 你编写的athlete表INSERT语句存在列值不匹配问题:列清单仅声明了9个字段(athleteFirstName到athleteNationality),但每行VALUES中都额外多传1个字符串类型的球队名。数据库会按照列的定义顺序逐值匹配,本该映射到整数类型
teamId字段的位置,被传入了“Toronto Blue Jays”这类无法转为整数的字符串,直接触发类型转换失败。
修复方案
不需要修改原有表结构,调整两处语句即可解决报错:
- 在INSERT的列清单中补上
teamId字段,保证列顺序与VALUES中的值顺序一一对应 - 不要在VALUES里硬写球队名字符串,通过子查询根据球队名匹配对应的teamId值,省去手动查询ID的繁琐操作
- 删除原代码中紧跟athlete表创建语句的
DROP TABLE athlete逻辑,避免刚创建的表被直接删除
修正后的athlete插入语句参考:
INSERT INTO athlete ( athleteFirstName, athleteLastName, athleteDateOfBirth, athleteHeight, athleteWeight, athletePosition, athleteBattingAvg, athleteNationality, teamId ) VALUES ('Vladimir', 'Guererro Jr.', '1999-03-16', 188, 113, '3B', 0.272, 'Canada', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Bo', 'Bichette', '1998-03-05', 183, 83, 'SS', 0.311, 'USA', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Cavan', 'Biggio', '1995-04-11', 188, 90, '2B', 0.234, 'USA', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Travis', 'Shaw', '1990-04-16', 193, 104, '1B', 0.157, 'USA', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Danny', 'Jansen', '1995-04-15', 188, 104, 'C', 0.207, 'USA', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Randal', 'Grichuk', '1991-08-13', 188, 96, 'OF', 0.232, 'USA', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Teoscar', 'Hernandez', '1992-10-15', 188, 92, 'OF', 0.230, 'Dominican Republic', (SELECT teamId FROM team WHERE teamName = 'Toronto Blue Jays')), ('Tim', 'Anderson', '1993-06-23', 185, 83, 'SS', 0.335, 'USA', (SELECT teamId FROM team WHERE teamName = 'Chicago White Sox')), ('Jose', 'Abreu', '1987-01-29', 190, 115, '1B', 0.284, 'Cuba', (SELECT teamId FROM team WHERE teamName = 'Chicago White Sox')), ('Kevin', 'Kiermaier', '1990-04-22', 185, 95, 'OF', 0.228, 'USA', (SELECT teamId FROM team WHERE teamName = 'Tampa Bay Rays')), ('Nolan', 'Arenado', '1991-04-16', 188, 97, '3B', 0.315, 'USA', (SELECT teamId FROM team WHERE teamName = 'Colorado Rockies')), ('Mike', 'Trout', '1991-08-07', 188, 106, 'OF', 0.291, 'USA', (SELECT teamId FROM team WHERE teamName = 'Los Angeles Angels')), ('Aaron', 'Judge', '1992-04-26', 201, 127, 'OF', 0.272, 'USA', (SELECT teamId FROM team WHERE teamName = 'New York Yankees')), ('Giancarlo', 'Stanton', '1989-11-08', 198, 111, 'OF', 0.288, 'USA', (SELECT teamId FROM team WHERE teamName = 'New York Yankees')), ('Joey', 'Votto', '1983-09-10', 188, 99, '1B', 0.261, 'Canada', (SELECT teamId FROM team WHERE teamName = 'Cincinnati Reds'));
内容的提问来源于stack exchange,提问作者YBRegular
相关产品推荐
相关产品推荐

