locked
SQL 2008 Overflowed an int but the value is..integer... RRS feed

  • Question

  • Hi guys, having

    create table #rete (mpo int)

    insert into rete (mpo) values ('2380001112857')

    The conversion of the varchar value '2380001112857' overflowed an int column

    Why? I'm inserting two million rows but I'm struck because this stupid issue. I don want put mpo as nvarchar because the tables I need to link later have mpo as int...

    Any sugegstion. Thank you

    Thursday, February 13, 2014 1:48 PM

Answers

  • Its out of range

    int

    -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

    4 Bytes

    http://technet.microsoft.com/en-us/library/ms187745.aspx


    Satheesh
    My Blog | How to ask questions in technical forum


    • Proposed as answer by Fanny Liu Friday, February 14, 2014 10:27 AM
    • Marked as answer by Fanny Liu Monday, February 24, 2014 9:58 AM
    Thursday, February 13, 2014 2:27 PM
  • You need to change the column data type to bigint to hold the value:

    create table #rete (mpo bigint)
    
    insert into #rete (mpo) values ('2380001112857')
    
    Select * from #rete
     
    
    drop table #rete


    • Proposed as answer by SQLZealots Thursday, February 13, 2014 2:24 PM
    • Marked as answer by Fanny Liu Monday, February 24, 2014 9:58 AM
    Thursday, February 13, 2014 1:57 PM
  • Hi,

    SQL Server largest int is 2147483647, so you overflowed it.

    Try declaring your mpo field as bigint instead.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by Olaf HelperMVP Thursday, February 13, 2014 2:24 PM
    • Marked as answer by Fanny Liu Monday, February 24, 2014 9:58 AM
    Thursday, February 13, 2014 2:18 PM

All replies

  • And now, even if I wiped away the mpo column it continues a give me :

    Arithmetic overflow error converting numeric to data type numeric.

    That's belong some other data...I gonna to check it....

    Thursday, February 13, 2014 1:51 PM
  • You need to change the column data type to bigint to hold the value:

    create table #rete (mpo bigint)
    
    insert into #rete (mpo) values ('2380001112857')
    
    Select * from #rete
     
    
    drop table #rete


    • Proposed as answer by SQLZealots Thursday, February 13, 2014 2:24 PM
    • Marked as answer by Fanny Liu Monday, February 24, 2014 9:58 AM
    Thursday, February 13, 2014 1:57 PM
  • thats because of some other large value in your insert which is greater than max range of numeric datatype

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, February 13, 2014 2:16 PM
  • Hi,

    SQL Server largest int is 2147483647, so you overflowed it.

    Try declaring your mpo field as bigint instead.


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Proposed as answer by Olaf HelperMVP Thursday, February 13, 2014 2:24 PM
    • Marked as answer by Fanny Liu Monday, February 24, 2014 9:58 AM
    Thursday, February 13, 2014 2:18 PM
  • Its out of range

    int

    -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

    4 Bytes

    http://technet.microsoft.com/en-us/library/ms187745.aspx


    Satheesh
    My Blog | How to ask questions in technical forum


    • Proposed as answer by Fanny Liu Friday, February 14, 2014 10:27 AM
    • Marked as answer by Fanny Liu Monday, February 24, 2014 9:58 AM
    Thursday, February 13, 2014 2:27 PM