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

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

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


当前回答

请尝试以下代码:

CREATE TABLE [dbo].[Department](
    [Department_name] char(10) NULL
)

INSERT INTO [dbo].[Department]([Department_name]) VALUES  ('Family Medicine')
--error will occur

 ALTER TABLE [Department] ALTER COLUMN [Department_name] char(50)

INSERT INTO [dbo].[Department]([Department_name]) VALUES  ('Family Medicine')

select * from [Department]

其他回答

我将添加另一个可能导致这个错误的原因,因为没有人提到过它,它可能会帮助一些未来的人(因为OP已经找到了他的答案)。如果要插入的表有触发器,则可能是触发器正在生成错误。我曾在更改表字段定义时看到过这种情况,但审计表没有更改。

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

正如其他人已经说过的,源表中的某个列数据类型大于目标列。

一个简单的解决方案是关闭警告并允许发生截断。所以,如果你收到这个错误,但你确定它是可以接受的数据在你的旧数据库/表被截断(削减到大小),你可以简单地做以下事情;

SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;

如上所述,始终记得在结束后再次打开警告。

是的,我也面临着这样的问题。

REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)

在这里,我将备注文件的长度从500更改为1000

至少将数据类型nchar(10)的长度更改为nchar(255)。