如何确定哪个列在插入时引发算术溢出错误？

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?

想象一下，里面有一百个不同的列。想象一下，我有一个用户数据表，我希望将数据复制到基表。所以我写了这个简单的insert-select语句，弹出这个错误。那么，找出哪一列引起错误的最优雅的方法是什么？

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:

我对解决方案的初步想法是将它包装在一个事务中，我将最终回滚并使用一种Divide and Conquer方法：

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.

注意：我也发现了这个问题的近似重复，但它几乎没有答案。

5 个解决方案

#1

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.

以下脚本将为Basetable的每个整数列创建SELECT语句。执行生成的SELECT语句应该精确定位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'

#2

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.

如果这只是您手动运行的内容，那么根据您插入的数据量，您可以使用OUTPUT子句将插入的行输出到客户端。

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

输出的最后一行之后的行应该是有问题的那一行。

#3

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.

我采用了Lieven Keersmaekers的方法但扩展了它。如果表具有各种数字字段长度，则此脚本将根据类型名称和精度更改Cast。考虑到这个解决方案，信用仍然留给了Lieven - 它给了我很多帮助。

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')

#4

-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.

对它运行查询，这样你就没有关于隐藏破坏它的列的transcation。有时在错误中它会给出关于什么是最新的提示。通常，如果您正在查看正在发生的事情，您可以看到文本何时进入int，反之亦然。

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.

您需要获取生成的查询的副本，您可以将其复制并粘贴到查询工具中。

#5

-1

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.

我认为你采取了错误的做法。如果通过简单地从一个表中选择列并插入另一个表来获得算术溢出，则必须从较大的colimns（例如bigint）中选择并插入小列（例如int）。这基本上是不正确的事情，你需要改变你的数据库结构，以便从一个表插入到另一个表中的行将起作用。检查每个表中的每一列，看看可能出现溢出的位置，然后调整目标表，以便插入的数据适合。

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).

如果你想要一个快速而肮脏的解决方案，我仍然认为我的观点仍然存在，但回应你的意见。在BaseTable varchar（MAX）中创建所有列。

Then:

然后：

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

#1