How to figure out which column raises an arithmetic overflow error upon insert?

Question

Imagine a table with like a hundred of different columns in it. Imagine, then, that I have a user-data table from where I want to copy data to the base table. So I wrote this simple insert-select statement and this error pops up. So, what's the most elegant way to figure out which column raises the error?

My initial thoughts on the solution are about wrapping it in a transaction that I will ultimately rollback and use a sort of Divide and Conquer approach:

begin tran

insert into BaseTable (c1,c2,c3,...,cN)
select c1,c2,c3,...,cN 
from UserTable

rollback tran

And this obviously fails. So we divide the column set in half like so:

begin tran

insert into BaseTable (c1,c2,c3,...,cK) --where K = N/2
select c1,c2,c3,...,cK --where K = N/2
from UserTable

rollback tran

And if it fails then the failing column is in the other half. And we continue the process, until we find the pesky column.

Anything more elegant than that?

Note: I also found a near-duplicate of this question but it barely answers it.

Answer 1

A lot of the times the brute force method you suggest is the best way.

However, if you have a copy of the database that you can post fake data too.

run the query on it so you don't have about the transcation hiding the column that is breaking it. Sometimes in the error it will give a hint as to what is up. Usually if you are looking at what is going in you can see when text is going into an int or vice versa.

I do this and that takes away anything else in my code causing the problem.

You need to get a copy of the query that is generated where you can copy and paste it into a query tool.

Answer 2

I think you are taking the wrong approach. If you are getting an arithmetic overflow by simply selecting columns from one table and inserting into another then you must be selecting from bigger colimns (e.g. bigint) and inserting into small columns (e.g. int). This is fundamentally incorrect thing to be doing and you need to alter your DB structure so that inserting rows from one table into another will work. Inspect each column from each table and see where it is possible to get an overflow, then adjust your destination table so that the data you are inserting will fit.

I still think my point stands but in response to your comments if you want a quick and dirty solution. Make all your columns in BaseTable varchar(MAX).

Then:

insert into BaseTable (c1,c2,...,cN)
select CAST(c1 AS varchar(max)),CAST(c2 AS varchar(max))...,cN 
from UserTable

Answer 3

Following script would create SELECT statements for each integer column of Basetable.
Executing the resulting SELECT statements should pinpoint the offending columns in your Usertable.

SELECT  'PRINT ''' 
        + sc.Name 
        + '''; SELECT MIN(CAST(' 
        + sc.Name 
        + ' AS INTEGER)) FROM Usertable'
FROM    sys.columns sc 
        INNER JOIN sys.types st ON st.system_type_id = sc.system_type_id
WHERE   OBJECT_NAME(Object_ID) = 'BaseTable'
        AND st.name = 'INT'

Answer 4

If this is just something you are running manually then depending upon how much data you are inserting you could use the OUTPUT clause to output the inserted rows to the client.

The row after the last one that is output should be the one with the problem.