How to figure out which column raises an arithmetic overflow error upon insert?
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.
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'
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.
I took Lieven Keersmaekers' approach but extended it. If a table has various numeric field lengths, this script will change the Cast based on the type name and precision. Credit still goes to Lieven for thinking of this solution - it helped me a lot.
DECLARE @tableName VARCHAR(100)
SET @tableName = 'tableName'
SELECT 'PRINT ''' + sc.NAME + '''; SELECT MIN(CAST([' + sc.NAME + '] as ' + CASE
WHEN st.NAME = 'int'
THEN 'int'
ELSE st.NAME + '(' + cast(sc.precision AS VARCHAR(5)) + ',' + cast(sc.scale AS VARCHAR(5)) + ')'
END + ')) from ' + @tableName
FROM sys.columns sc
INNER JOIN sys.types st ON st.system_type_id = sc.system_type_id
WHERE OBJECT_NAME(Object_ID) = @tableName
AND st.NAME NOT IN ('nvarchar', 'varchar', 'image', 'datetime', 'smalldatetime', 'char', 'nchar')
下一篇: 如何确定插入时哪一列会引发算术溢出错误？