时间戳

首页 > 解决方案 > 在 SQL 中插入多个值，转换失败

sql-server - 在 SQL 中插入多个值，转换失败

问题描述

我有一个 SQL 表：

CREATE TABLE [jato].[option_list](
    [vehicle_id] [bigint] NOT NULL,
    [option_id] [int] NOT NULL,
    [option_type] [varchar](10) NULL,
    [option_code] [varchar](255) NULL,
    [manuf_name] [nvarchar](255) NULL,
    [id_902] [float] NULL,
    [id_903] [float] NULL,
    [id_904] [float] NULL,
    [id_905] [float] NULL,
    [id_100902] [float] NULL,
    [id_100903] [float] NULL,
    [id_100904] [float] NULL,
    [id_100905] [float] NULL,
 CONSTRAINT [PK_OPTIONLIST] PRIMARY KEY CLUSTERED 
(
    [vehicle_id] ASC,
    [option_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

我想在其中插入值。

以下查询有效：

INSERT INTO jato.option_list 
(vehicle_id,option_id,option_type,option_code,manuf_name,id_902,id_903,id_904,id_905,id_100902,id_100903,id_100904,id_100905) 

values
(11280320191201,1142,'C','MG','Machine Grey Metallic',0,0,0,0,0,0,0,0)

但是如果我在同一个查询中添加其他值，我会遇到从varchar到 int 的转换失败，而我的列顺序没有改变。例如以下查询不起作用，当 SQL 将“MG”转换为 int 时出现错误：

INSERT INTO jato.option_list 
(vehicle_id,option_id,option_type,option_code,manuf_name,id_902,id_903,id_904,id_905,id_100902,id_100903,id_100904,id_100905) 

values
(11280320191201,1142,'C','MG','Machine Grey Metallic',0,0,0,0,0,0,0,0),
(11688620170510,1190,'O',881,'Automatic boot pull down',635.25,525,0,0,635.25,525,0,0)

我不明白我做错了什么。

有人有什么主意吗？

标签： sql-serverinsert

解决方案

您的问题在于第二行值：

(11688620170510,1190,'O',881,'Automatic boot pull down',635.25,525,0,0,635.25,525,0,0)

将其更改为：

(11688620170510,1190,'O','881','Automatic boot pull down',635.25,525,0,0,635.25,525,0,0)

然后它将起作用。总而言之，您的代码应该是这样的：

INSERT INTO dbo.option_list 
(vehicle_id,option_id,option_type,option_code,manuf_name,id_902,id_903,id_904,id_905,id_100902,id_100903,id_100904,id_100905) 

values
(11280320191201,1142,'C','MG','Machine Grey Metallic',0,0,0,0,0,0,0,0),
(11688620170510,1190,'O','881','Automatic boot pull down',635.25,525,0,0,635.25,525,0,0)

推荐阅读