none
Arithmetic overflow error converting numeric to data type numeric. RRS feed

  • Question

  • hello 

    I am transferring data from one DB to another in the same instance and the error is

     

    Msg 8115, Level 16, State 8, Line 1

    Arithmetic overflow error converting numeric to data type numeric.

    The statement has been terminated.

    Actually the query i am using is

    insert WS.dbo.APP
     (AGENCYID, APPOINTMENTID,APPOINTMENTTIME,RESOURCECOLUMN) 
    select A_AGENCY_ID,APPOINTMENT_ID,START_TIME, DURATION
     FROM WST.dbo.APPOINTMENTS

    how do i resolve this eror

    tHANKS IN ADVANCE

     


    dimrd_SQL
    • Changed type SQL_BOSS Wednesday, December 15, 2010 9:57 PM GENERAL
    • Changed type SQL_BOSS Wednesday, December 15, 2010 9:58 PM MAKING SPECIFIC TO AREA
    Tuesday, December 14, 2010 8:08 PM

Answers

  • Hi SQL_BOSSSINGH,

     

    In such situation, I would like to recommend that you try converting the type of column DURATION during this process. I have modified your code as below:

    INSERT WS.dbo.APP(AGENCYID, APPOINTMENTID, APPOINTMENTTIME, RESOURCECOLUMN)

    SELECT A_AGENCY_ID, APPOINTMENT_ID, START_TIME, CAST(DURATION AS NUMERIC(2,0))

    FROM WST.dbo.APPOINTMENTS

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Thursday, December 16, 2010 8:44 AM
    Moderator

All replies

  • Hello,

    Try to make the source data types and the destination data types match.

     http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Hope this helps.   
     
    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, December 16, 2010 12:22 AM
    Moderator
  • tHANKS Alberto

    I will explain you the datatype of the column at the source is NUMERIC(3,0) and the datatype of the destination is NUMERIC(2,0) AND I TRIED  to change the destination datatype as follows ALTER TABLE dbo.APPOINTMENT ALTER COLUMN RESOURCECOLUMN NUMERIC(3,0).

    I mean changing to NUMERIC(3,0) FROM NUMERIC(2,0) BUT I AM GETTING THIS ERROR

    Msg 5074, Level 16, State 1, Line 1

    The index 'IX_APPOINTMENT_2' is dependent on column 'RESOURCECOLUMN'.

    Msg 4922, Level 16, State 9, Line 1

    ALTER TABLE ALTER COLUMN RESOURCECOLUMN failed because one or more objects access this column.

    sO PLEASE HELP ME ASAP
    THANKS


    dimrd_SQL
    Thursday, December 16, 2010 3:39 AM
  • Hi SQL_BOSSSINGH,

     

    In such situation, I would like to recommend that you try converting the type of column DURATION during this process. I have modified your code as below:

    INSERT WS.dbo.APP(AGENCYID, APPOINTMENTID, APPOINTMENTTIME, RESOURCECOLUMN)

    SELECT A_AGENCY_ID, APPOINTMENT_ID, START_TIME, CAST(DURATION AS NUMERIC(2,0))

    FROM WST.dbo.APPOINTMENTS

     

    If anything is unclear, please let me know.


    Regards,
    Tom Li
    Thursday, December 16, 2010 8:44 AM
    Moderator
  • Wednesday, December 22, 2010 5:31 PM