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

  • Question

  • I am trying to bulk insert from one table to another having too many columns but when I try to insert the following error is coming. it is very hard to find which columns   is having trouble . How can I figure out which column is being with conversion error. 

    BEGIN TRY

    Insert into

    table

    ( Column1, Column2........ Column60)

    select Column1,Column2.... from table2

    END TRY

     BEGIN CATCH
    SELECT 
    ERROR_NUMBER() AS ErrorNumber
            ,ERROR_SEVERITY() AS ErrorSeverity
            ,ERROR_STATE() AS ErrorState
            ,ERROR_PROCEDURE() AS ErrorProcedure
            ,ERROR_LINE() AS ErrorLine
            ,ERROR_MESSAGE() AS ErrorMessage
    END CATCH


    Please can you help is there any method to figure out which column causing the problem

     

    Regards

    Pol


    polachan

    Tuesday, February 26, 2019 11:04 AM

All replies

  • Check the data type of all columns to see where is a mismatch = type of target is smaller then source and causes the error.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, February 26, 2019 11:46 AM
  • Is there anyway to know the column name of the table  affecting this  error while inserting 

    Pol


    polachan

    Tuesday, February 26, 2019 12:20 PM
  • Hi Pol,

     

    Thank you for your posting .

     

    Per your description , I think it might be difficult to fulfill your needs, at least I don't have any ideas about it. In your error message , Errorline returns the line number of occurrence of an error that caused the CATCH block of a TRY...CATCH construct to execute. After that , please check the data type of all columns to see where is a mismatch or which is smaller than source and causes the error column by column. This might be the best way.

     

    Best Regards,
    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, February 27, 2019 2:38 AM
  • Hi Pol,

    I have a way for you.

    Check this out.

    I have a source table called Src having two columns. Also I have a table called Dest to which data is being copied to. I receive an Arithmetic Overflow error.

    Here is how we can find out which column. We have to create a perm table Temp to do this.

    Code:

    Set NoCount Off
    Set NoCount On
    Go
    If Object_ID('Src','U') Is Not NUll
    	Drop Table Src
    GO
    Create Table Src
    (
    	[Col1] Decimal(2,1),
    	[Col2] Numeric(2,1)
    )
    GO
    Begin Try
    	Insert Src
    	Values (0.1,1.1)
    End Try
    Begin Catch
    	SELECT ERROR_NUMBER() AS ErrorNumber
    	,ERROR_SEVERITY() AS ErrorSeverity
    	,ERROR_STATE() AS ErrorState
    	,ERROR_LINE() AS ErrorLine
    	,Convert(varchar(80),ERROR_MESSAGE())  AS ErrorMessage
    End Catch
    GO
    If Object_ID('Dest','U') Is Not NUll
    	Drop Table Dest
    GO
    Create Table Dest
    (
    	[Col1] Decimal(2,1),
    	[Col2] Numeric(1,1)
    )
    GO
    Begin Try
    	Insert Dest
    	([Col1],[Col2])
    	Select [Col1], [Col2]
    	From Src
    End Try
    Begin Catch
    	SELECT 'Error copying Data From Src to Dest' CustomMessage
    	,ERROR_NUMBER() AS ErrorNumber
    	,ERROR_SEVERITY() AS ErrorSeverity
    	,ERROR_STATE() AS ErrorState
    	,ERROR_LINE() AS ErrorLine
    	,Convert(varchar(80),ERROR_MESSAGE())  AS ErrorMessage
    End Catch
    GO
    If Object_ID('Temp','U') Is Not NUll
    	Drop Table Temp
    GO
    Create Table Temp
    (
    	ID Int Identity(1,1) Primary Key
    )
    GO
    Declare @Col nvarchar(50), @Sql nvarchar(40)
    
    Declare Cur Cursor For
    	Select 'Alter Table Temp Add ' + name + ' ' + TYPE_NAME(user_type_id) + ' (' + convert(varchar,precision) + ',' + convert(varchar,scale) + ') ',
    	'Insert Temp(' + name + ') Select [' + name + '] From Src' 
    	From sys.columns Where OBJECT_NAME(object_id) = 'Dest'
    
    Open Cur
    
    Fetch Next From Cur Into @Col, @Sql
    
    While @@FETCH_STATUS = 0
    Begin
    	
    
    	Exec sp_executesql @Col
    	Begin Try
    		Exec sp_executesql @Sql
    	End Try
    	Begin Catch
    		SELECT @Sql ColumnCausingProblem
    		,ERROR_NUMBER() AS ErrorNumber
    		,ERROR_SEVERITY() AS ErrorSeverity
    		,ERROR_STATE() AS ErrorState
    		,ERROR_LINE() AS ErrorLine
    		,Convert(varchar(80),ERROR_MESSAGE())  AS ErrorMessage
    	End Catch
    
    	Fetch Next From Cur Into @Col, @Sql
    
    End
    
    Close Cur
    
    Deallocate Cur
    GO

    Results:

    CustomMessage                       ErrorNumber ErrorSeverity ErrorState  ErrorLine   ErrorMessage
    ----------------------------------- ----------- ------------- ----------- ----------- --------------------------------------------------------------------------------
    Error copying Data From Src to Dest 8115        16            8           2           Arithmetic overflow error converting numeric to data type numeric.
    
    ColumnCausingProblem                     ErrorNumber ErrorSeverity ErrorState  ErrorLine   ErrorMessage
    ---------------------------------------- ----------- ------------- ----------- ----------- --------------------------------------------------------------------------------
    Insert Temp(Col2) Select [Col2] From Src 8115        16            8           1           Arithmetic overflow error converting numeric to data type numeric.
    
    Sharat

    • Edited by SharatGupta Wednesday, February 27, 2019 1:09 PM
    • Proposed as answer by SharatGupta Wednesday, February 27, 2019 4:29 PM
    Wednesday, February 27, 2019 1:09 PM