我参与了一个数据迁移项目。当我试图将数据从一个表插入到另一个表(SQL Server 2005)时,我得到以下错误:

编号8152,16层,状态13,1线 字符串或二进制数据将被截断。

源数据列与数据类型匹配,并且在目标表列的长度定义内,因此我不知道是什么原因导致了这个错误。


当前回答

One other potential reason for this is if you have a default value setup for a column that exceeds the length of the column. It appears someone fat fingered a column that had a length of 5 but the default value exceeded the length of 5. This drove me nuts as I was trying to understand why it wasn't working on any insert, even if all i was inserting was a single column with an integer of 1. Because the default value on the table schema had that violating default value it messed it all up - which I guess brings us to the lesson learned - avoid having tables with default value's in the schema. :)

其他回答

One other potential reason for this is if you have a default value setup for a column that exceeds the length of the column. It appears someone fat fingered a column that had a length of 5 but the default value exceeded the length of 5. This drove me nuts as I was trying to understand why it wasn't working on any insert, even if all i was inserting was a single column with an integer of 1. Because the default value on the table schema had that violating default value it messed it all up - which I guess brings us to the lesson learned - avoid having tables with default value's in the schema. :)

您需要发布源表和目标表的表定义,以便我们找出问题所在,但底线是源表中的一列比目标列大。这可能是您正在以一种您没有意识到的方式更改格式。要弄清楚这一点,您要使用的数据库模型也很重要。

这可能是一个具有挑战性的错误。以下是AmirCharania在https://connect.microsoft.com/SQLServer/feedback/details/339410/网站上发表的评论。

我调整了AmirCharania给出的答案,用于选择实际表中的数据,而不是临时表。首先选择你的数据集到一个开发表中,然后运行以下命令:

WITH CTE_Dev
AS (
    SELECT C.column_id
        ,ColumnName = C.NAME
        ,C.max_length
        ,C.user_type_id
        ,C.precision
        ,C.scale
        ,DataTypeName = T.NAME
    FROM sys.columns C
    INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
    WHERE OBJECT_ID = OBJECT_ID('YOUR TARGET TABLE NAME HERE, WITH SCHEMA')
    )
    ,CTE_Temp
AS (
    SELECT C.column_id
        ,ColumnName = C.NAME
        ,C.max_length
        ,C.user_type_id
        ,C.precision
        ,C.scale
        ,DataTypeName = T.NAME
    FROM sys.columns C
    INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
    WHERE OBJECT_ID = OBJECT_ID('YOUR TEMP TABLE NAME HERE, WITH SCHEMA')
    )
SELECT *
FROM CTE_Dev D
FULL OUTER JOIN CTE_Temp T ON D.ColumnName = T.ColumnName
WHERE ISNULL(D.max_length, 0) < ISNULL(T.max_length, 999)

我今天遇到了这个问题,在我搜索这个信息量最小的错误消息的答案时,我还发现了这个链接:

https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name

所以微软似乎没有计划在短时间内扩展错误信息。

所以我转向了其他方法。

我把错误复制到excel中:

(1行受影响)

(1行受影响)

(1行受影响) 编号8152,16层,14号状态,13行 字符串或二进制数据将被截断。 声明已终止。

(1行受影响)

计算excel中的行数,接近导致问题的记录计数器…调整我的导出代码打印出SQL接近它…然后运行5 - 10个SQL插入问题SQL,并设法查明问题之一,看到字符串太长,增加该列的大小,然后大导入文件运行没有问题。

这是一种破解和变通方法,但当你别无选择时,你只能做你能做的。

在Acumatica ERP中,我在导入订单时得到了相同的错误。

字符串或二进制数据将被截断在表'MyDatabase.dbo。ARInvoice',列'InvoiceNbr'。截断值“Something”。

在做了这个链接中描述的步骤如何修复字符串或二进制截断错误后,我得到了另一个错误“数量将变成负数”,这是通过在收据屏幕中创建一些数量来解决的。