SQL Server字符串或二进制数据将被截断

I am involved in a data migration project. I am getting the following error when I try to insert data from one table into another table (SQL Server 2005):

我参与了一个数据迁移项目。当我试图将一个表中的数据插入到另一个表(SQL Server 2005)时，会出现以下错误:

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.

Msg 8152，级别16，状态13，第一行字符串或二进制数据将被截断。

The source data columns match the data type and are within the length definitions of the destination table columns so I am at a loss as to what could be causing this error.

源数据列与数据类型匹配，并且在目标表列的长度定义中，因此我对可能导致这个错误的原因感到不知所措。

17 个解决方案

#1

You will need to post the table definitions for the source and destination tables for us to figure out where the issue is but the bottom line is that one of your columns in the source table is bigger than your destination columns. It could be that you are changing formats in a way you were not aware of. The database model you are moving from is important in figuring that out as well.

您需要将表定义发布到源表和目标表中，以便找出问题所在，但底线是源表中的某个列比目标列更大。可能是您正在以一种您没有意识到的方式更改格式。你正在从的数据库模型也很重要。

#2

The issue is quite simple: one or more of the columns in the source query contains data that exceeds the length of its destination column. A simple solution would be to take your source query and execute Max(Len( source col )) on each column. I.e.,

问题很简单:源查询中的一个或多个列包含超过其目标列长度的数据。一个简单的解决方案是获取源查询并在每个列上执行Max(Len(source col))。也就是说,

Select Max(Len(TextCol1))
    , Max(Len(TextCol2))
    , Max(Len(TextCol3))
    , ...
From ...

Then compare those lengths to the data type lengths in your destination table. At least one, exceeds its destination column length.

然后将这些长度与目标表中的数据类型长度进行比较。至少一个，超过它的目标列长度。

If you are absolutely positive that this should not be the case and do not care if it is not the case, then another solution is to forcibly cast the source query columns to their destination length (which will truncate any data that is too long):

如果您是绝对肯定的，这不应该是这种情况，也不关心它是不是这种情况，那么另一个解决方案是强制将源查询列强制转换到它们的目标长度(这将截断太长时间的任何数据):

Select Cast(TextCol1 As varchar(...))
    , Cast(TextCol2 As varchar(...))
    , Cast(TextCol3 As varchar(...))
    , ...
From ...

#3

As others have already said, one of your columns datatypes in the source table is larger than your destination columns.

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

Because no one has mentioned it here, a simple solution (similar to Thomas' CAST solution) is to simply turn off the warning and allow truncation to take place. So, if you're receiving this error but you are sure it is acceptable for data in your old database/table to be truncated (cut to size) you can simply do the following;

因为这里没有人提到它，一个简单的解决方案(类似于Thomas的CAST解决方案)就是关闭警告并允许执行截断。因此，如果您正在接收这个错误，但是您确信旧数据库/表中的数据被截断(按大小切割)是可以接受的，您可以简单地执行以下操作;

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

As above, always remember to turn warnings back on again afterwards. I hope this helps.

如上所述，永远记住之后再次打开警告。我希望这可以帮助。

#4

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

另一个可能的原因是，如果一个列的默认值设置超过了列的长度。似乎有人用手指触摸了一个长度为5的列，但默认值超过了5。这让我抓狂，因为我试图理解为什么它不能在任何插入操作上工作，即使我所插入的只是一个整数为1的列。因为表模式上的默认值违反了默认值，这就把它搞砸了——我想这给我们上了一课——避免在模式中使用具有默认值的表。:)

#5

This can be a challenging error. Here are some notes taken from https://connect.microsoft.com/SQLServer/feedback/details/339410/ look for AmirCharania's comment.

这可能是一个具有挑战性的错误。下面是一些来自https://connect.microsoft.com/sqlserver/feedback/details/339410/查找AmirCharania的注释。

I've adjusted the answer given by AmirCharania for data selected into an actual table, instead of a temp one. First select your dataset into a development table then run the following:

我已经调整了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)

#6

Yes,I am also face these kind of problem.

是的，我也面临着这些问题。

REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)

Here, I've change REMARKS filed length from 500 to 1000

这里，我将文件长度从500更改为1000

#7

this can also happen when you dont have adequate permissions

当您没有足够的权限时，这也会发生

#8

For the others, also check your stored procedure. In my case in my stored procedure CustomSearch I accidentally declared not enough length for my column, so when I entered a big data I received that error even though I have a big length on my database. I just changed the length of my column in my custom search the error goes away. This is just for the reminder. Thanks.

对于其他的，也要检查存储过程。在我的存储过程CustomSearch中，我不小心为我的列声明了不够长的长度，所以当我输入一个大数据时，我收到了这个错误，尽管我的数据库有很大的长度。我只是在自定义搜索中更改了列的长度，错误就消失了。这只是为了提醒大家。谢谢。

#9

I came across this problem today, and in my search for an answer to this minimal informative error message i also found this link:

我今天遇到了这个问题，在寻找这个信息最少的错误信息的答案时，我也找到了这个链接:

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

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

So it seems microsoft has no plans to expand on error message anytime soon.

因此，微软似乎没有计划在近期扩展错误信息。

So i turned to other means.

于是我转向其他方法。

I copied the errors to excel:

我将错误复制到excel中:

(1 row(s) affected)

(1行受影响)

(1 row(s) affected)

(1行受影响)

(1 row(s) affected) Msg 8152, Level 16, State 14, Line 13 String or binary data would be truncated. The statement has been terminated.

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

(1 row(s) affected)

(1行受影响)

counted the number of rows in excel, got to close to the records counter that caused the problem... adjusted my export code to print out the SQL close to it... then ran the 5 - 10 sql inserts around the problem sql and managed to pinpoint the problem one, see the string that was too long, increase size of that column and then big import file ran no problem.

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

Bit of a hack and a workaround, but when you left with very little choice you do what you can.

一些小窍门和变通方法，但当你只剩下很少的选择时，你就能做你能做的。

#10

I've built a stored procedure that analyses a source table or query with several characteristics per column among which the minimum length (min_len) and maximum length (max_len).

我构建了一个存储过程，该过程分析一个源表或查询，每个列具有几个特征，其中最小长度(min_len)和最大长度(max_len)。

CREATE PROCEDURE [dbo].[sp_analysetable] (
  @tableName varchar(8000),
  @deep bit = 0
) AS

/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/

DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0

IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
  DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
  DROP TABLE ##tmpColumns
END

if CHARINDEX('from', @tableName)>0
  set @isQuery=1

IF @intErrorCode=0 BEGIN
  if @isQuery=1 begin
    --set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
    --replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
    set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
    exec(@tableName)
    IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
      set @intErrorCode=1
      SET @errorMSG='Error generating temporary table from query.'
    end
    else begin
      set @tableName='##tmpTableToAnalyse'
    end
  end
end

IF @intErrorCode=0 BEGIN
  SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
  select
    c.column_name as [column],
    cast(sp.value as varchar(1000)) as description,
    tc_fk.constraint_type,
    kcu_pk.table_name as fk_table,
    kcu_pk.column_name as fk_column,
    c.ordinal_position as pos,
    c.column_default as [default],
    c.is_nullable as [null],
    c.data_type,
    c.character_maximum_length as length,
    c.numeric_precision as [precision],
    c.numeric_precision_radix as radix,
    cast(null as bit) as [is_unique],
    cast(null as int) as min_len,
    cast(null as int) as max_len,
    cast(null as int) as nulls,
    cast(null as int) as blanks,
    cast(null as int) as numerics,
    cast(null as int) as distincts,
    cast(null as varchar(500)) as distinct_values,
    cast(null as varchar(50)) as remarks
  into ##tmpColumns'
  if @isQuery=1 begin
    SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
  end
  else begin
    SET @tmpQ=@tmpQ+'
      from information_schema.columns c
      left join sysobjects so    on so.name=c.table_name  and so.xtype=''U''
      left join syscolumns sc    on sc.name=c.column_name and sc.id  =so.id 
      left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''  
      left join information_schema.key_column_usage kcu_fk    on kcu_fk.table_name = c.table_name     and c.column_name = kcu_fk.column_name
      left join information_schema.table_constraints tc_fk    on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
      left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
      left join information_schema.table_constraints tc_pk    on rc.unique_constraint_name = tc_pk.constraint_name
      left join information_schema.key_column_usage kcu_pk    on tc_pk.constraint_name = kcu_pk.constraint_name
 '
  end
  SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''

  exec(@tmpQ)
end

IF @intErrorCode=0 AND @deep = 1 BEGIN
  DECLARE
    @count_rows int,
    @count_distinct int,
    @count_nulls int,
    @count_blanks int,
    @count_numerics int,
    @min_len int,
    @max_len int,
    @distinct_values varchar(500)
  DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
    select [column] from ##tmpColumns;
  OPEN curTmp
  FETCH NEXT FROM curTmp INTO @column_name
  WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
    set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
      '  @count_rows=count(0), '+char(13)+char(10)+
      '  @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
      '  @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
      '  @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
      '  @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
      '  @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
      '  @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
      ' from ['+@tableName+']'
    exec sp_executesql @tmpQ,
                       N'@count_rows int OUTPUT,
                         @count_distinct int OUTPUT,
                         @count_nulls int OUTPUT,
                         @count_blanks int OUTPUT,
                         @count_numerics int OUTPUT,
                         @min_len int OUTPUT,
                         @max_len int OUTPUT',
                       @count_rows     OUTPUT,
                       @count_distinct OUTPUT,
                       @count_nulls    OUTPUT,
                       @count_blanks    OUTPUT,
                       @count_numerics OUTPUT,
                       @min_len        OUTPUT,
                       @max_len        OUTPUT

    IF (@count_distinct>10) BEGIN
      SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
    END ELSE BEGIN
      set @distinct_values=null
      set @tmpQ = N'USE '+DB_NAME()+';'+
        '  select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar),  cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
        '  from ('+char(13)+char(10)+
        '    select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
      exec sp_executesql @tmpQ,
                         N'@distinct_values varchar(500) OUTPUT',
                         @distinct_values        OUTPUT
    END
    UPDATE ##tmpColumns SET
      is_unique      =case when @count_rows=@count_distinct then 1 else 0 end,
      distincts      =@count_distinct,
      nulls          =@count_nulls,
      blanks         =@count_blanks,
      numerics       =@count_numerics,
      min_len        =@min_len,
      max_len        =@max_len,
      distinct_values=@distinct_values,
      remarks       =
        case when @count_rows=@count_nulls then 'all null,' else '' end+
        case when @count_rows=@count_distinct then 'unique,' else '' end+
        case when @count_distinct=0 then 'empty,' else '' end+
        case when @min_len=@max_len then 'same length,' else '' end+
        case when @count_rows=@count_numerics then 'all numeric,' else '' end
    WHERE [column]=@column_name

    FETCH NEXT FROM curTmp INTO @column_name
  END
  CLOSE curTmp DEALLOCATE curTmp
END

IF @intErrorCode=0 BEGIN
  select * from ##tmpColumns order by pos
end

IF @intErrorCode=0 BEGIN --Clean up temporary tables
  IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
    DROP TABLE ##tmpTableToAnalyse
  END
  IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
    DROP TABLE ##tmpColumns
  END
end

IF @intErrorCode<>0 BEGIN
  RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode

I store this procedure in the master database so that I can use it in every database like so:

我将此过程存储在主数据库中，以便在每个数据库中使用，如下所示:

sp_analysetable 'table_name', 1
// deep=1 for doing value analyses

And the output is:

和输出是:

column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric, guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric, id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,

列描述constraint_type fk_table fk_column pos违约零data_type长度精度基数is_unique min_len max_len null空格的数字明显distinct_values言论id_individual零主键为空零1零不int零10 10 1 1 2 0 0 70 70(70)许多独特的,所有的数字,id_brand零空零空2空不int零10 70 0 1 1 0 0 2 2 3长度相同,所有的数字,guid零空零空3(newid())没有uniqueidentifier零空零1 36 36 0 0 0 70多(70)独一无二的,同样的长度,customer_id空零空零4零是的varchar 50空零0零空70 0 0 0零所有空,空,空邮件零空零5空是的varchar 100空零36 0 4 0 0 0 31许多(31)移动空零空零6空是的varchar 50空零0零空70 0 0 0零所有空,空的,首字母空零空零7零是的varchar 50空零0零空70 0 0 0零所有空,空,title_short空零空零8零是的varchar 50空零0零空70 0 0 0零所有空,空,title_long空零空零9零是的varchar 50空零0零空70 0 0 0零所有空,空,firstname空零空零10空是的varchar 50空零0零空70 0 0 0零所有空,空的,lastname空零空零11零是的varchar 50空零0零空70 0 0 0零所有空,空的,地址空零空零12个零是的varchar 100空零0零空70 0 0 0零空,空的,电脑空零空零13零是的varchar 10空零0零空70 0 0 0零空,空,kixcode空零空零14空是的varchar 20空零0零空70 0 0 0零所有空,空的,date_created空零空零15(获取当前日期())没有datetime零空零1 19日19日0 0 0 70多(70)独一无二的,同样的长度,created_by空零空零16(user_name())没有varchar 50空零0 13 13 0 0 0 1 loyalz-public长度相同,id_location_created零外键位置id_location 17零是的int零10 70 0 1 1 0 0 2 1 2长度相同,所有的数字,id_individual_type空外键person类型id_individual_type类型18零，是的，int NULL 0 0 0 0 0 0 0 0 0 0 0 0 0 0所有空，optin NULL 0零零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零，零

#11

I am going to add one other possible cause of this error just because no one has mentioned it and it might help some future person (since the OP has found his answer). If the table you are inserting into has triggers, it could be the trigger is generating the error. I have seen this happen when table field definitions were changed, but audit tables were not.

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

#12

I was using empty string '' on on table creation and then receiving error 'Msg 8152, String or binary data would be truncated' on subsequent update. This was happening due to the update value containing 6 characters and being larger than the column definition anticipated. I used "SPACE" to get around this only because I knew I would be updating in bulk following the initial data creation i.e. the column was not going to remain empty for long.

我在创建表时使用了空字符串，然后在后续更新时接收到错误的msg8152，字符串或二进制数据将被截断。这是由于包含6个字符的更新值大于列定义的预期值。我使用“空格”来解决这个问题，只是因为我知道，在初始数据创建之后，我将大量更新，也就是说，列不会长期保持为空。

SO BIG CAVEAT HERE: This is not a particularly slick solution but is useful in the case where you are pulling together a data set e.g. for one-off intelligence requests where you are creating a table for data mining, applying some bulk processing/interpretation and storing before and after results for later comparison/mining. This is a frequent occurrence in my line of work.

这么大的警告:这不是一个特别的解决方案但非常有用的情况下你汇总数据集如一次性情报请求你在哪里创建表为数据挖掘应用一些批量处理/解释和存储之前和之后的结果,以便稍后进行比较/采矿。这在我的工作中很常见。

You can initially populate using the SPACE keyword i.e.

您可以使用SPACE关键字例如。

    select 
           Table1.[column1]
          ,Table1.[column2]
          ,SPACE(10) as column_name
    into table_you_are_creating
    from Table1
    where ...

Subsequent updates to "column_name" of 10 characters or less (substitute as applicable) will then be allowed without causing truncate error. Again, I would only use this in scenarios similar to that described in my caveat.

随后将允许对10个字符或更少的“column_name”进行后续更新(如适用)，而不会导致截断错误。同样，我将只在类似于我的警告中所描述的场景中使用它。

#13

I had a similar issue. I was copying data from one table to an identical table in everything but name.

我也有类似的问题。我把数据从一个表复制到一个完全相同的表，除了名字。

Eventually I dumped the source table into a temp table using a SELECT INTO statement.

最后，我使用SELECT into语句将源表转储到临时表中。

SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;

I compared the schema of the source table to temp table. I found one of the columns was a varchar(4000) when I was expecting a varchar(250).

我将源表的模式与临时表进行了比较。我发现其中一列是varchar(4000)，当时我正期待varchar(250)。

UPDATE: The varchar(4000) issue can be explained here in case you are interested:

更新:varchar(4000)问题可以在这里解释，如果你有兴趣:

For Nvarchar(Max) I am only getting 4000 characters in TSQL?

对于Nvarchar(Max)，我只能在TSQL中获得4000个字符?

Hope this helps.

希望这个有帮助。

#14

This error is thrown when the column of a table puts constraint [ mostly length ]. . E.g. if database schema for column myColumn is CHAR(2), then when your call from any of your application to insert value, you must pass String of length two.

当表的列放置约束(大部分是长度)时抛出此错误。例如，如果列myColumn的数据库模式是CHAR(2)，那么当您从任何应用程序调用插入值时，必须传递长度为2的字符串。

The error basically says it; string of length three and above is inconsistent to fit the length restriction specified by database schema. That's why SQL Server warns and throws data loss/ Truncation error.

错误基本上是这样说的;长度为3或3以上的字符串不符合数据库模式指定的长度限制。这就是为什么SQL Server会发出警告并抛出数据丢失/截断错误。

#15

Yep - "a pint into a half-pint pot will not go". I've not had much luck (for whatever reason) with the various SPs that folks have suggested, BUT as long as the two tables are in the same DB (or you can get them into the same DB), you can use INFORMATION_SCHEMA.COLUMNS to locate the errant field(s), thusly:

是的——“一品脱半品脱的啤酒是不会走的”。我没有多少幸运(不管出于什么原因)和人们建议的不同的SPs，但是只要两个表在同一个DB(或者您可以把它们放到同一个DB中)，您就可以使用INFORMATION_SCHEMA。列来定位错误字段(s)， thusly:

select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c1.CHARACTER_MAXIMUM_LENGTH,c2.table_name,c2.COLUMN_NAME, c2.DATA_TYPE,c2.CHARACTER_MAXIMUM_LENGTH
from [INFORMATION_SCHEMA].[COLUMNS] c1
left join [INFORMATION_SCHEMA].[COLUMNS] c2 on 
c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME='MyTable1'
and c2.TABLE_NAME='MyTable2'
--and c1.DATA_TYPE<>c2.DATA_TYPE
--and c1.CHARACTER_MAXIMUM_LENGTH <> c2.CHARACTER_MAXIMUM_LENGTH
order by c1.COLUMN_NAME

This will let you scroll up and down, comparing field lengths as you go. The commented sections let you see (once uncommented, obviously) if there are data type mismatches, or specifically show those that differ in field length - cos I'm too lazy to scroll - just be aware that the whole thing is predicated on the source column names matching those of the target.

这将使您上下滚动，比较字段的长度。评论部分让你看到(一旦激活,显然)如果有数据类型不匹配,或者专门展示那些字段长度不同,因为我懒得滚动,只是知道整件事是基于源列名称匹配的目标。

#16

Here is a slightly different answer. Your column names & lengths may all match, but perhaps you are specifying the columns in the wrong order in your SELECT statement. Say tableX and tableY have columns with the same name, but in different order

这里有一个稍微不同的答案。您的列名称和长度可能都匹配，但您可能在SELECT语句中指定了错误顺序的列。假设tableX和tableY的列名称相同，但顺序不同

#17

Please try the following code:

请尝试以下代码:

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]