带有SQL_VARIANT的多行INSERT：将数据类型varchar转换为numeric时出错 [英] Multi-line INSERT with SQL_VARIANT: Error converting data type varchar to numeric

问题描述

有谁可以告诉我为什么这个多行INSERT在SQL Server 2017 Developer Edition上不起作用？

Can anyone tell me why this multi-line INSERT doesn't work on SQL Server 2017 Developer Edition?

SET ANSI_NULL_DFLT_OFF ON;
DECLARE @t TABLE (
    Isn INTEGER
   ,Seq SMALLINT
   ,[ParameterName] VARCHAR(128)
   ,[JobParameter] SQL_VARIANT
);
INSERT INTO @t
 ( Isn, Seq, ParameterName, JobParameter) VALUES
 (11275,2,'ProcessingQtr', CAST('ABCDE' AS CHAR(5))),
 (11275,3,'SalesType', CAST(1 AS NUMERIC(1)))
;

奇怪的是，如果只有一行插入一个时间，它的工作原理。这就是为什么我尝试在同一个INSERT语句中插入两行而导致错误的原因：

What's odd is that if only one line at a time is inserted, it works. It's only why I try to insert two lines in the same INSERT statement that I get the error:

Msg 8114，Level 16，State 5，Line 7

将数据类型varchar转换为数字时出错。

Msg 8114, Level 16, State 5, Line 7
Error converting data type varchar to numeric.

推荐答案

INSERT INTO @t

 （Isn，Seq，ParameterName，JobParameter）VALUES

 （11275,2， 'ProcessingQtr'，CAST（'ABCDE'AS CHAR（5））），

 （11275,3，'SalesType'，CAST（1 AS NUMERIC（1）））

INSERT INTO @t
 ( Isn, Seq, ParameterName, JobParameter) VALUES
 (11275,2,'ProcessingQtr', CAST('ABCDE' AS CHAR(5))),
 (11275,3,'SalesType', CAST(1 AS NUMERIC(1)))

这与

INSERT INTO @t

   SELECT 11275,2，'ProcessingQtr'，CAST（'ABCDE'AS CHAR（5））

   UNION ALL

   SELECT 11275,3，'SalesType'，CAST（1 AS NUMERIC（1））

INSERT INTO @t
   SELECT 11275,2,'ProcessingQtr', CAST('ABCDE' AS CHAR(5))
   UNION ALL
   SELECT 11275,3,'SalesType', CAST(1 AS NUMERIC(1))

在查询中，列中的所有行都具有相同的数据类型。如果UNION ALL的不同分支对于列具有不同的数据类型，则优先级较低的类型将转换为具有最高优先级的类型。在这种情况下，char在
列表中较低且数字较高，因此varchar转换为numeric。流言结束。

In a query, all rows in a column have the same data type. If different branches of a UNION ALL has different data types for a column, the types with lower precedence will be converted to the type with the highest precedence. In this case, char is low on the list and numeric is higher, so varchar gets converted to numeric. Which ends in tears.

现在，您正在插入一个sql_variant列，但是直到您进入插入才会发生转换，这已经太晚了。你需要向sql_variant添加一个显式的强制转换：

Now, you are inserting into an sql_variant column, but that conversion does not occur until you come to the insert and that is too late. You need to add a explicit cast to sql_variant:

INSERT INTO @t

 （Isn，Seq，ParameterName，JobParameter）VALUES

 （11275,2，'ProcessingQtr'，CAST（CAST（'ABCDE'AS CHAR（5））AS sql_variant）），

 （11275,3，'SalesType' ，CAST（CAST（1 AS NUMERIC（1））AS sql_variant）））
;

INSERT INTO @t
 ( Isn, Seq, ParameterName, JobParameter) VALUES
 (11275,2,'ProcessingQtr', CAST (CAST('ABCDE' AS CHAR(5)) AS sql_variant)),
 (11275,3,'SalesType', CAST (CAST(1 AS NUMERIC(1)) AS sql_variant))
;

如果你很懒，你可以在第一行做到，由于sql_variant位于优先级列表的顶部，因此所有内容都将转换为sql_variant。但最好将它放在所有行上。

If you are lazy, you can do it for the first row, since sql_variant is on the top of the precedence list, so everything will be converted to sql_variant. But it is probably better to have it on all rows.

这篇关于带有SQL_VARIANT的多行INSERT：将数据类型varchar转换为numeric时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！