Arithmetic overflow error for type varchar

Question

This is my query:

insert into [Consolidated_LoB].[dbo].[consold]
    ([shp_to_pstl_cd]
      ,[LoB]
      ,[catlg_nbr]
      ,[shpd_qty])
   select 
      [POSTAL_CODE]
      ,[LoB]
      ,[PART]
      ,[SHIPPED_QUANTITY]
   from 
      [ITB_Pank].[dbo].[ITB_consoldtd]

and the message I receive is:

Arithmetic overflow error for type varchar, value = 436112652.000000.

Please advise

CREATE TABLE [dbo].[consold]
(
    [srce_loc] [char](3) NULL,
    [q2c_nbr] [char](8) NULL,
    [ln_itm] [char](3) NULL,
    [mfg_loc] [char](3) NULL,
    [shpd_qty] [int] NULL,
    [shpg_loc] [char](3) NULL,
    [inv_prof_loc] [char](3) NULL,
    [shp_to_pstl_cd] [char](10) NULL,
    [shpmnt_dt] [datetime] NULL,
    [shpmnt_typ_ind] [char](2) NULL,
    [tot_typ] [char](2) NULL,
    [catlg_nbr] [char](34) NULL,
    [LoB] [char](10) NULL
    [del_id] [varchar](12) NULL
)

CREATE TABLE [dbo].[ITB_consoldtd]
(
    [POSTAL_CODE] [float] NULL,
    [LoB] [char](10) NULL,
    [DELIVERY_ID] [float] NULL,
    [PART] [nvarchar](255) NULL,
    [SHIPPED_QUANTITY] [float] NULL
)

To simplify the mapping exercise, this is source to target

- [POSTAL_CODE]      [float]         -> [shp_to_pstl_cd] [char](10)
- [LoB]              [char](10)      -> [LoB]            [char](10)
- [PART]             [nvarchar](255) -> [catlg_nbr]      [char](34)
- [SHIPPED_QUANTITY] [float]         -> [shpd_qty]       [int]

armitage · Accepted Answer · 2015-02-20 15:24:52Z

An "Arithmetic overflow" occurs when you are converting data from one data type, ConvertFrom to a different type, ConvertTo and the datatype of ConvertTo cannot store the data coming from ConvertFrom

i.e. ConvertFrom INT has a value of 234,567 which you are assigning to ConverTo TINYINT.

As TINYINT only allows values 0 to 255 you get an "Arithmetic overflow" error.

In your example above you have multiple type conversions that may result in an "Arithmetic overflow" error i.e.

[POSTAL_CODE]      [float]         -> [shp_to_pstl_cd] [char](10)
[PART]             [nvarchar](255) -> [catlg_nbr]      [char](34)
[SHIPPED_QUANTITY] [float]         -> [shpd_qty]       [int]

In the case of POSTAL_CODE, this will only work if the size of the FLOAT field contains no more then 10 digits including the decimal point where as the FLOAT fields supports up to a 53 digit number.

In the case of PART this will only work if the there are 34 characters or less.

In the case of SHIPPED_QUANTITY its a bit more complicated for instance 123456 will convert as will 123456.789 but the data will convert as 123456 loosing the digits after the decimal point which could be critical.

You need to review your table and use common data types for storing common data.

Stack Exchange Network

Arithmetic overflow error for type varchar

1 Answer 1

Your Answer

Hot Network Questions

Arithmetic overflow error for type varchar

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions