-1

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]
1

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.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.