在插入/更新时，将数据类型varchar转换为数字的错误

I was trying to insert/update records in bulk using merge statement. But I am getting following error. I have searched online i couldn't find a way to fix. Can some one tell me where I am doing wrong?

我试图使用merge语句批量插入/更新记录。但我有以下错误。我在网上搜索，找不到解决办法。有人能告诉我我哪里做错了吗?

Error converting data type varchar to numeric while inserting'

在插入时将数据类型varchar转换为数值的错误。

Table structure

表结构

 CREATE TABLE [Metric].[MetricGoal]
 (
    [metricGoalId] [int] IDENTITY(1,1) NOT NULL,
    [profileConfigId] [int] NOT NULL,
    [metricGoalName] [varchar](200) NULL,
    [metricIndicatorId] [int] NOT NULL,
    [marketConfigId] [int] NOT NULL,
    [regionConfigId] [int] NOT NULL,
    [goalYearConfigId] [int] NOT NULL,
    [goalPeriodConfigId] [int] NOT NULL,
    [targetValue] [decimal](20, 3) NULL,
    [actualValue] [decimal](20, 3) NULL,
    [metricGoalStatusConfigId] [int] NOT NULL,
    [metricGoalStatusReasonConfigId] [int] NOT NULL,
    [ownerId] [int] NULL,
    [workerId] [int] NULL,
    [createdOn] [datetime] NOT NULL,
    [createdBy] [int] NOT NULL,
    [updatedOn] [datetime] NOT NULL,
    [updatedBy] [int] NOT NULL,
    [lineOfBusinessConfigId] [int] NULL,
    [productConfigId] [int] NULL,
    [serviceAreaConfigId] [int] NULL,
  )

User Defined Table Type(created type with only columns that needs to insert / update):

用户定义的表类型(只创建需要插入/更新的列的类型):

CREATE TYPE [Metric].[MetricGoalType3] AS TABLE
(
    [metricGoalId] [int] NULL,
    [lineOfBusinessConfigId] [int] NULL,    
    [metricIndicatorId] [int] NOT NULL,
    [goalYearConfigId] [int] NOT NULL,
    [goalPeriodConfigId] [int] NOT NULL,
    [marketConfigId] [int] NOT NULL,
    [targetValue] [decimal](20, 3) NULL,
    [actualValue] [decimal](20, 3) NULL,
    [metricGoalStatusConfigId] [int] NOT NULL,
    [metricGoalStatusReasonConfigId] [int] NOT NULL,
    [ownerId] [int] NULL,
    [workerId] [int] NULL
)

Stored procedure to insert/update using Merge:

使用Merge插入/更新的存储过程:

CREATE PROCEDURE [Metric].[prMaintainMetricGoalBulkLoad]
    @currUserId INT = NULL,
    @currProfileConfigId INT = NULL,
    @tblMetricGoal [Metric].[MetricGoalType3] READONLY
AS
    DECLARE @now DATETIME = GETDATE()
BEGIN
    SET NOCOUNT ON;

    MERGE INTO [Metric].[MetricGoal] T
    USING @tblMetricGoal S ON (T.metricGoalId = S.metricGoalId)

    WHEN MATCHED 
       THEN UPDATE
            SET T.targetValue = CASE WHEN S.targetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),T.targetValue) END, 
                T.actualValue = CASE WHEN S.actualValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),T.actualValue) END,  
                T.metricGoalStatusConfigId = CASE WHEN S.metricGoalStatusConfigId =  -1 THEN NULL ELSE ISNULL(S.metricGoalStatusConfigId,T.metricGoalStatusConfigId) END, 
                T.metricGoalStatusReasonConfigId = CASE WHEN S.metricGoalStatusReasonConfigId = -1 THEN NULL ELSE ISNULL(S.metricGoalStatusReasonConfigId,T.metricGoalStatusReasonConfigId) END, 
                T.ownerId = CASE WHEN S.ownerId = -1 THEN NULL ELSE ISNULL(S.ownerId,T.ownerId) END, 
                T.workerId = CASE WHEN S.workerId = -1 THEN NULL ELSE ISNULL(S.workerId,T.workerId) END, 
                T.updatedOn = @now,
                T.updatedBy = @currUserId

WHEN NOT MATCHED BY TARGET
    THEN INSERT (profileConfigId,
                 --metricGoalName,
                 metricIndicatorId, lineOfBusinessConfigId, marketConfigId,
                 --productConfigId,
                 --serviceAreaConfigId,
                 --regionConfigId,
                 goalYearConfigId, goalPeriodConfigId, targetValue, actualValue,
                 metricGoalStatusConfigId, metricGoalStatusReasonConfigId,
                 ownerId, workerId, createdOn,  createdBy, 
                 updatedOn, updatedBy)
         VALUES (@currProfileConfigId,
                 --S.metricGoalName,
                 S.metricIndicatorId, S.lineOfBusinessConfigId, S.marketConfigId,
                 --NULLIF(S.productConfigId,-1),
                 --NULLIF(S.serviceAreaConfigId,-1),
                 --S.regionConfigId,
                 S.goalYearConfigId, S.goalPeriodConfigId,
                 CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),
                 CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),
                 S.metricGoalStatusConfigId, S.metricGoalStatusReasonConfigId,
                 NULLIF(S.ownerId, -1),
                 NULLIF(S.workerId, -1),
                 @now, @currUserId, @now, @currUserId);
END

Execution (used SQL Server Profiler to get below statements)

执行(使用SQL Server Profiler获取语句)

declare @p3 Metric.MetricGoalType3
insert into @p3 values(820,819,4,602,570,694,39.000,43.000,655,660,1585,NULL)
insert into @p3 values(NULL,819,4,602,570,1853,NULL,NULL,655,660,NULL,NULL)

exec Metric.prMaintainMetricGoalBulkLoad @currUserId=1618,@currProfileConfigId=301,@tblMetricGoal=@p3

3 个解决方案

#1

These 2 rows contain error:

这两行包含错误:

T.targetValue = CASE WHEN S.targetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,'')),T.targetValue) END, 
T.actualValue = CASE WHEN S.actualValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,'')),T.actualValue) END,

Both targetValue and actualValue are decimal(20,3), so how can you use '' that is string with decimals?

targetValue和actualValue都是decimal(20,3)，那么如何使用“that is string with decimal ?”

It should be

它应该是

T.targetValue = CASE WHEN S.targetValue is null THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.targetValue,null)),T.targetValue) END, 
T.actualValue = CASE WHEN S.actualValue is null THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(S.actualValue,null)),T.actualValue) END,

even it has no sense but at least there vill be no conversion from varchar to numeric.

即使它没有意义，但至少不会有从varchar到数字的转换。

Here is how to reproduce your error (I'll use variables instead of tables):

下面是如何重现您的错误(我将使用变量而不是表):

declare @StargetValue DECIMAL(20,3) = 10, @TtargetValue DECIMAL(20,3) = 20;
set @ttargetValue = CASE WHEN @StargetValue = '' THEN NULL ELSE ISNULL(CONVERT(DECIMAL(20,3),NULLIF(@StargetValue,'')),@TtargetValue) END

When you try to confront your decimal with '' you get the error because '' just cannot be converted to decimal.

当你试图用“你得到错误”来面对你的小数时，因为不能把它转换成小数。

#2

There is data that will not convert correctly, exactly what or where I can't tell but since you indicate you are using SQL Server 2012 then use TRY_CONVERT or TRY_CAST instead of convert or cast.

有数据不能正确地转换，确切地说，我不能说什么，但是因为您指示您使用SQL Server 2012，然后使用TRY_CONVERT或TRY_CAST而不是convert或cast。

These 2 newer functions do not cause a failure if the data cannot be converted, they simply return NULL instead. While this may not solve bad data it does help. For example you can use these in a where clause e.g.

如果数据不能被转换，这两个更新的函数不会导致失败，而是返回NULL。虽然这可能不能解决坏数据，但确实有帮助。例如:you can use these in a where子句中。

 select * 
 from to_be_imported
 where try_convert(date,stringcolumn) IS NULL

#3

It looks like the insert portion of the statement does not align with your table definition. The third field in your insert list is lineOfBusinessConfigId which is trying to insert into the third field of the table, a varchar column metricGoalName.

看起来语句的insert部分与表定义不一致。插入列表中的第三个字段是lineOfBusinessConfigId，它试图插入到表的第三个字段，varchar列metricGoalName。

#1