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]

closed as off-topic by Max Vernon, RolandoMySQLDBA, Colin 't Hart, Mark Sinkinson, Michael Green Sep 16 '15 at 10:19

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RolandoMySQLDBA, Colin 't Hart, Mark Sinkinson, Michael Green
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What are the table definitions? Datatypes on all columns in the tables? – Taryn Feb 19 '15 at 15:46
  • 8
    my advice: use correct datatypes and then use appropriate conversions and then use appropriate error handling. – swasheck Feb 19 '15 at 15:50
  • 1
    If [SHIPPED_QUANTITY] is always a whole number, perhaps you could cast [SHIPPED_QUANTITY] to an integer before inserting it into your [consold] table. – mmarie Feb 19 '15 at 15:50
  • 6
    Converting POSTAL_CODE from a FLOAT to a CHAR doesn't look great? – Mark Sinkinson Feb 19 '15 at 16:01
  • 2
    Bad Habits to Kick: Choosing the wrong data type - sqlblog.com/blogs/aaron_bertrand/archive/2009/10/12/… – Mark Sinkinson Feb 19 '15 at 16:11

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.

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