blocks|key|3171870|text|您需要发布源表和目标表的表定义，以便我们找出问题所在，但底线是源表中的某一列大于目标列。这可能是因为您正在以一种您没有意识到的方式更改格式。您要迁移的数据库模型在弄清楚这一点方面也很重要。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3171871|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|3171923|text|问题非常简单:源查询中的一个或多个列包含的数据超过了其目标列的长度。一个简单的解决方案是获取源查询并对每一列执行Max(Len(+source+col+))。也就是说，|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3171924|Select+Max(Len(TextCol1))
++++,+Max(Len(TextCol2))
++++,+Max(Len(TextCol3))
++++,+...
From+...|code-block|syntax|javascript|3171925|然后将这些长度与目标表中的数据类型长度进行比较。至少有一个超过其目标列长度。|3171926|如果您绝对肯定不应该出现这种情况，并且不关心是否出现这种情况，那么另一种解决方案是将源查询列强制转换为它们的目标长度(这将截断任何过长的数据)：|3171927|Select+Cast(TextCol1+As+varchar(...))
++++,+Cast(TextCol2+As+varchar(...))
++++,+Cast(TextCol3+As+varchar(...))
++++,+...
From+...|3171928|entityMap^0|1K|M|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|X|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|Y|8|@]|D|@]|E|$I|J]]|$1|Q|3|-4|5|6|7|Z|8|@]|D|@]|E|$]]]|R|$]]

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 <code>Max(Len( source col ))</code> on each column. I.e., 

<pre><code>Select Max(Len(TextCol1))
 , Max(Len(TextCol2))
 , Max(Len(TextCol3))
 , ...
From ...
</code></pre>

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

<pre><code>Select Cast(TextCol1 As varchar(...))
 , Cast(TextCol2 As varchar(...))
 , Cast(TextCol3 As varchar(...))
 , ...
From ...
</code></pre>

blocks|key|2957944|text|对于其他的，还要检查您的存储过程。在我的例子中，在我的存储过程CustomSearch中，我意外地声明了列的长度不够，所以当我输入一个大数据时，即使我的数据库中有很大的长度，我也会收到这个错误。我刚刚在自定义搜索中更改了列的长度，错误就消失了。这只是为了提醒一下。谢谢。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2957945|entityMap^0|V|C|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

For the others, also check your stored procedure. In my case in my stored procedure <code>CustomSearch</code> 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.

blocks|key|3171971|text|当您没有足够的权限时，也会发生这种情况|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3171972|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

this can also happen when you dont have adequate permissions

blocks|key|3172007|text|正如其他人已经说过的，源表中的某个列数据类型大于目标列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3172008|一个简单的解决方案是简单地关闭警告并允许发生截断。所以，如果你收到这个错误，但是你确定你的旧数据库/表中的数据被截断(裁剪成一定大小)是可以接受的，你可以简单地执行以下操作；|3172009|SET+ANSI_WARNINGS+OFF;
--+Your+insert+TSQL+here.
SET+ANSI_WARNINGS+ON;|code-block|syntax|javascript|3172010|如上所述，始终记得在之后再次打开警告。我希望这能帮到你。|3172011|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

As others have already said, one of your columns datatypes in the source table is larger than your destination columns.
A simple 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;
<pre><code>SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;
</code></pre>
As above, always remember to turn warnings back on again afterwards. I hope this helps.

blocks|key|2958019|text|我今天遇到了这个问题，在搜索这个最小信息性错误消息的答案时，我还找到了这个链接：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2958020|https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name|offset|length|2958021|因此，微软似乎没有计划在短期内扩展错误消息。|2958022|所以我转向了其他方法。|2958023|我将错误复制到excel中：|2958024|(1行受影响)|2958025|2958026|(1行受影响)消息8152，级别16，状态14，行13字符串或二进制数据将被截断。该语句已终止。|2958027|2958028|计算excel中的行数，接近导致问题的记录计数器...调整了我的导出代码，以打印出接近它的SQL+...然后在有问题的sql周围运行5-+10个sql插入，并设法找到有问题的sql，查看太长的字符串，增加该列的大小，然后运行大的导入文件没有问题。|2958029|一些技巧和变通方法，但是当你离开时，你几乎没有选择，你可以做你能做的。|2958030|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|47|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@$D|14|E|15|1|16]]|A|$]]|$1|F|3|G|5|6|7|17|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|18|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|19|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1A|8|@]|9|@]|A|$]]|$1|N|3|M|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1C|8|@]|9|@]|A|$]]|$1|Q|3|M|5|6|7|1D|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1E|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1F|8|@]|9|@]|A|$]]|$1|V|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|W|$X|$5|Y|Z|10|A|$11|C]]]]

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

<a href="https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name" rel="nofollow">https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name</a>

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:

(1 row(s) affected)

(1 row(s) affected)

(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 row(s) affected)

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.

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

blocks|key|3172086|text|出现这种情况的另一个潜在原因是，如果您为超过列长度的列设置了默认值。似乎有人用胖手指指出了长度为5的列，但默认值超过了5的长度。这让我抓狂，因为我试图理解为什么它不能在任何insert上工作，即使我插入的只是一个整数为1的列。因为表模式上的默认值违反了默认值，所以把它搞乱了--我猜这给我们带来了教训--避免在模式中有缺省值的表。:)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3172087|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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

blocks|key|2958160|text|这可能是一个具有挑战性的错误。以下是来自https://connect.microsoft.com/SQLServer/feedback/details/339410/+look的一些笔记，以了解AmirCharania的评论。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2958161|我调整了AmirCharania对选择到实际表中的数据的答案，而不是临时表中的数据。首先将您的数据集选择到一个开发表中，然后运行以下命令：|2958162|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)|code-block|syntax|javascript|2958163|entityMap|0|LINK|mutability|MUTABLE|url|https://connect.microsoft.com/SQLServer/feedback/details/339410/^0|K|1S|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|6|7|W|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|X|8|@]|9|@]|C|$I|J]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

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

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:

<pre><code>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) &lt; ISNULL(T.max_length, 999)
</code></pre>

blocks|key|2958180|text|是的，我也面临着这样的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2958181|REMARKS+VARCHAR(500)
to
REMARKS+VARCHAR(1000)|code-block|syntax|javascript|2958182|在这里，我将备注文件长度从500更改为1000|2958183|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Yes,I am also face these kind of problem. 

<pre><code>REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)
</code></pre>

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

blocks|key|264519|text|我构建了一个存储过程来分析源表或查询，每个列有几个特征，其中包括最小长度(min_len)和最大长度(max_len)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|264520|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+'%2B@db%2B';'%2Breplace(@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+'%2BDB_NAME()%2B';'%2BCHAR(13)%2BCHAR(10)%2B'
++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%2B'+from+tempdb.information_schema.columns+c,+(select+null+as+value)+sp'
++end
++else+begin
++++SET+@tmpQ=@tmpQ%2B'
++++++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%2B'+where+c.table_name+=+'''%2B@tableName%2B''''

++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+'%2BDB_NAME()%2B';+SELECT'%2B
++++++'++@count_rows=count(0),+'%2Bchar(13)%2Bchar(10)%2B
++++++'++@count_distinct=count(distinct+['%2B@column_name%2B']),'%2Bchar(13)%2Bchar(10)%2B
++++++'++@count_nulls=sum(case+when+['%2B@column_name%2B']+is+null+then+1+else+0+end),'%2Bchar(13)%2Bchar(10)%2B
++++++'++@count_blanks=sum(case+when+ltrim(['%2B@column_name%2B'])=''''+then+1+else+0+end),'%2Bchar(13)%2Bchar(10)%2B
++++++'++@count_numerics=sum(isnumeric(['%2B@column_name%2B'])),'%2Bchar(13)%2Bchar(10)%2B
++++++'++@min_len=min(len(['%2B@column_name%2B'])),'%2Bchar(13)%2Bchar(10)%2B
++++++'++@max_len=max(len(['%2B@column_name%2B']))'%2Bchar(13)%2Bchar(10)%2B
++++++'+from+['%2B@tableName%2B']'
++++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+('%2Bcast(@count_distinct+as+varchar)%2B')'
++++END+ELSE+BEGIN
++++++set+@distinct_values=null
++++++set+@tmpQ+=+N'USE+'%2BDB_NAME()%2B';'%2B
++++++++'++select+@distinct_values=COALESCE(@distinct_values%2B'',''%2Bcast(['%2B@column_name%2B']+as+varchar),++cast(['%2B@column_name%2B']+as+varchar))'%2Bchar(13)%2Bchar(10)%2B
++++++++'++from+('%2Bchar(13)%2Bchar(10)%2B
++++++++'++++select+distinct+['%2B@column_name%2B']+from+['%2B@tableName%2B']+where+['%2B@column_name%2B']+is+not+null)+a'%2Bchar(13)%2Bchar(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%2B
++++++++case+when+@count_rows=@count_distinct+then+'unique,'+else+''+end%2B
++++++++case+when+@count_distinct=0+then+'empty,'+else+''+end%2B
++++++++case+when+@min_len=@max_len+then+'same+length,'+else+''+end%2B
++++++++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|code-block|syntax|javascript|264521|我将此过程存储在master数据库中，以便可以在每个数据库中使用它，如下所示：|264522|sp_analysetable+'table_name',+1
//+deep=1+for+doing+value+analyses|264523|输出结果为：|264524|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|offset|length|style|CODE|264525|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,|264526|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,|264527|customer_id+++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++4+++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264528|email+++++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++5+++NULL++++++++++YES++varchar++++++++++100++++NULL++++++NULL++0+++++++++4+++++++36++++++0+++++0++++++0++++++++31++++++++Many+(31)|264529|mobile++++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++6+++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264530|initials++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++7+++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264531|title_short+++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++8+++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264532|title_long++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++9+++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264533|firstname+++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++10++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264534|lastname++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++11++NULL++++++++++YES++varchar++++++++++50+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264535|address+++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++12++NULL++++++++++YES++varchar++++++++++100++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264536|pc++++++++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++13++NULL++++++++++YES++varchar++++++++++10+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264537|kixcode+++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++14++NULL++++++++++YES++varchar++++++++++20+++++NULL++++++NULL++0+++++++++NULL++++NULL++++70++++0++++++0++++++++0+++++++++NULL++++++++++++all+null,empty,|264538|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,|264539|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,|264540|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,|264541|optin+++++++++++++++NULL++++++++NULL++++++++++++NULL++++++++++++NULL+++++++++++++++19++NULL++++++++++YES++int++++++++++++++NULL+++10++++++++10++++0+++++++++1+++++++1+++++++39++++0++++++31+++++++2+++++++++0,1+++++++++++++same+length,|264542|entityMap^0|0|0|0|0|0|0|6B|0|0|6N|0|0|DG|0|0|6J|0|0|5X|0|0|6J|0|0|6J|0|0|6J|0|0|6J|0|0|6J|0|0|6J|0|0|6J|0|0|6J|0|0|6J|0|0|6N|0|0|6G|0|0|DC|0|0|6G|0^^$0|@$1|2|3|4|5|6|7|1S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|1V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|1W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|1X|8|@$O|1Y|P|1Z|Q|R]]|9|@]|A|$]]|$1|S|3|T|5|6|7|20|8|@$O|21|P|22|Q|R]]|9|@]|A|$]]|$1|U|3|V|5|6|7|23|8|@$O|24|P|25|Q|R]]|9|@]|A|$]]|$1|W|3|X|5|6|7|26|8|@$O|27|P|28|Q|R]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|29|8|@$O|2A|P|2B|Q|R]]|9|@]|A|$]]|$1|10|3|11|5|6|7|2C|8|@$O|2D|P|2E|Q|R]]|9|@]|A|$]]|$1|12|3|13|5|6|7|2F|8|@$O|2G|P|2H|Q|R]]|9|@]|A|$]]|$1|14|3|15|5|6|7|2I|8|@$O|2J|P|2K|Q|R]]|9|@]|A|$]]|$1|16|3|17|5|6|7|2L|8|@$O|2M|P|2N|Q|R]]|9|@]|A|$]]|$1|18|3|19|5|6|7|2O|8|@$O|2P|P|2Q|Q|R]]|9|@]|A|$]]|$1|1A|3|1B|5|6|7|2R|8|@$O|2S|P|2T|Q|R]]|9|@]|A|$]]|$1|1C|3|1D|5|6|7|2U|8|@$O|2V|P|2W|Q|R]]|9|@]|A|$]]|$1|1E|3|1F|5|6|7|2X|8|@$O|2Y|P|2Z|Q|R]]|9|@]|A|$]]|$1|1G|3|1H|5|6|7|30|8|@$O|31|P|32|Q|R]]|9|@]|A|$]]|$1|1I|3|1J|5|6|7|33|8|@$O|34|P|35|Q|R]]|9|@]|A|$]]|$1|1K|3|1L|5|6|7|36|8|@$O|37|P|38|Q|R]]|9|@]|A|$]]|$1|1M|3|1N|5|6|7|39|8|@$O|3A|P|3B|Q|R]]|9|@]|A|$]]|$1|1O|3|1P|5|6|7|3C|8|@$O|3D|P|3E|Q|R]]|9|@]|A|$]]|$1|1Q|3|-4|5|6|7|3F|8|@]|9|@]|A|$]]]|1R|$]]

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

<pre><code>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)&gt;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&gt;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&lt;&gt;0 BEGIN
 RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode
</code></pre>

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

<pre><code>sp_analysetable 'table_name', 1
// deep=1 for doing value analyses
</code></pre>

And the output is:

<code>
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,
</code>

blocks|key|2958257|text|我在创建表时使用空字符串'‘on，然后在后续更新时收到错误'Msg+8152，字符串或二进制数据将被截断’。发生这种情况的原因是更新值包含6个字符，并且大于预期的列定义。我使用"SPACE“来解决这个问题，只是因为我知道我将在初始数据创建之后进行批量更新，即该列不会长时间保持为空。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2958258|这里有一个很大的警告:这不是一个特别巧妙的解决方案，但在你正在收集数据集的情况下很有用，例如，对于一次性的情报请求，你正在为数据挖掘创建一个表，应用一些批量处理/解释，并在之前和之后存储结果以供以后比较/挖掘。这在我的工作中是经常发生的。|2958259|最初可以使用空格关键字进行填充。|2958260|++++select+
+++++++++++Table1.[column1]
++++++++++,Table1.[column2]
++++++++++,SPACE(10)+as+column_name
++++into+table_you_are_creating
++++from+Table1
++++where+...|code-block|syntax|javascript|2958261|随后，将允许对10个或更少字符的"column_name“进行后续更新(如果适用，请替换)，而不会导致截断错误。同样，我只会在类似于我的警告中描述的场景中使用它。|2958262|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Q|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|R|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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.

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.

<pre><code> select 
 Table1.[column1]
 ,Table1.[column2]
 ,SPACE(10) as column_name
 into table_you_are_creating
 from Table1
 where ...
</code></pre>

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.

blocks|key|264596|text|我也遇到过类似的问题。我将数据从一个表复制到除名称之外的所有表中的相同表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|264597|最后，我使用SELECT+into语句将源表转储到一个临时表中。|264598|SELECT+*
INTO+TEMP_TABLE
FROM+SOURCE_TABLE;|code-block|syntax|javascript|264599|我比较了源表和临时表的模式。我发现其中一列是varchar(4000)，而我期望的是varchar(250)。|offset|length|style|CODE|264600|更新:如果你感兴趣，可以在这里解释varchar(4000)问题：|264601|For+Nvarchar(Max)+I+am+only+getting+4000+characters+in+TSQL?|264602|希望这能有所帮助。|264603|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql/1371584#1371584^0|0|0|0|M|D|16|C|0|0|0|1O|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|13|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|14|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|15|8|@$K|16|L|17|M|N]|$K|18|L|19|M|N]]|9|@]|A|$]]|$1|O|3|P|5|6|7|1A|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1B|8|@]|9|@$K|1C|L|1D|1|1E]]|A|$]]|$1|S|3|T|5|6|7|1F|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|V|$W|$5|X|Y|Z|A|$10|11]]]]

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.

<pre><code>SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;
</code></pre>

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

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

<a href="https://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql/1371584#1371584">For Nvarchar(Max) I am only getting 4000 characters in TSQL?</a> 

Hope this helps.

blocks|key|2958297|text|我将添加这个错误的另一个可能的原因，因为没有人提到它，它可能会对未来的某个人有所帮助(因为OP已经找到了答案)。如果要插入到的表有触发器，则可能是触发器生成了错误。我见过当表字段定义被更改时发生这种情况，但审计表没有发生这种情况。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2958298|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

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.

blocks|key|266143|text|请尝试以下代码：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|266144|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]|code-block|syntax|javascript|266145|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Please try the following code:

<pre><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]
</code></pre>

blocks|key|3172307|text|当表的列将约束主要放在长度上时，将抛出此错误。。例如，如果列myColumn的数据库模式是CHAR(2)，那么当您从任何应用程序调用插入值时，您必须传递长度为2的字符串。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3172308|错误基本上说明了这一点；长度大于等于3的字符串不符合数据库模式指定的长度限制。这就是SQL+Server发出警告并抛出数据丢失/截断错误的原因。|3172309|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

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.

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.

blocks|key|2958367|text|是的--“半品脱壶里的一品脱不去”。对于人们建议的各种s，我不太幸运(不管是什么原因)，但只要这两个表在同一个数据库中(或者您可以将它们放到同一个数据库中)，您就可以使用INFORMATION_SCHEMA.COLUMNS来定位错误字段，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2958368|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|code-block|syntax|javascript|2958369|这将允许您向上和向下滚动，比较字段长度。注释部分允许您查看(显然，一旦未注释)是否存在数据类型不匹配，或者特别显示字段长度不同的字段-因为我懒得滚动-只是要知道，整个操作都是基于与目标列名称匹配的源列名称。|2958370|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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:

<pre><code>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&lt;&gt;c2.DATA_TYPE
--and c1.CHARACTER_MAXIMUM_LENGTH &lt;&gt; c2.CHARACTER_MAXIMUM_LENGTH
order by c1.COLUMN_NAME
</code></pre>

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.

blocks|key|3172348|text|这是一个稍有不同的答案。您的列名和长度可能都匹配，但您可能在SELECT语句中指定了错误的列顺序。假设tableX和tableY具有同名的列，但顺序不同|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3172349|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Here is a slightly different answer. Your column names &amp; 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

blocks|key|2958431|text|SQL+Server+2019最终将返回更有意义的错误消息。|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|2958432|2958433|+Binary+or+string+data+would+be+truncated+=>+error+message+enhancments|blockquote|2958434|2958435|如果您有这个错误(在生产中)，很难清楚地看到这个错误来自哪一列或哪一行，以及如何准确定位它。|2958436|2958437|要启用新行为，您需要使用DBCC+TRACEON(460)。来自sys.messages的新错误文本|CODE|2958438|SELECT+*+FROM+sys.messages+WHERE+message_id+=+2628|code-block|syntax|javascript|2958439|2958440|+2628+-将在表‘’%251！‘’的列‘’%253！‘’中截断字符串或二进制数据。截断值：‘%251！’。|2958441|2958442|2958443|String+or+Binary+data+would+be+truncated:+replacing+the+infamous+error+8152|2958444|2958445|这条新消息也会回传到SQL+Server2017+CU12+(以及即将发布的SQL+Server2016+SP2+CU中)，但不是默认的。您需要启用跟踪标志460，以便在会话或服务器级别将消息ID+8152替换为2628。|2958446|2958447|请注意，目前，即使在SQL+Server+2019+CTP+2.0中，也需要启用相同的跟踪标志460。在未来的SQL+Server+2019版本中，默认情况下，消息2628将替换消息8152。|2958448|2958449|2958450|SQL+Server+2017+CU12也支持此功能。|2958451|Improvement:+Optional+replacement+for+"String+or+binary+data+would+be+truncated"+message+with+extended+information+in+SQL+Server+2017|2958452|2958453|此SQL+Server2017更新引入了一条可选消息，其中包含以下附加上下文信息。|2958454|2958455|MSG2628，Level+16，State+6，Procedure+ProcedureName，Line+Linenumber字符串或二进制数据将在表‘%251！’的列‘%253！’中被截断。截断值：‘’%251！‘’。|2958456|新消息ID为2628。如果启用跟踪标志460，则此消息将替换任何错误输出中的消息8152。|2958457|2958458|2958459|2958460|2958461|++|2958462|2958463|VERBOSE_TRUNCATION_WARNINGS+={+ON+%7C+OFF+}|2958464|适用于:+SQL+Server+(从SQL+Server+2019+(15.x)开始)和Azure+SQL数据库|2958465|允许您启用或禁用新字符串或二进制数据将被截断的错误消息。SQL+Server+2019+(15.x)针对此方案引入了新的、更具体的错误消息(2628)：|2958466|String+or+binary+data+would+be+truncated+in+table+'%25.*ls',+column'%25.*ls'.+Truncated+value:+'%25.*ls'.|2958467|在数据库兼容级别150下设置为ON时，截断错误将引发新的错误消息2628，以提供更多上下文并简化故障排除过程。|2958468|在数据库兼容级别150下设置为OFF时，截断错误将引发先前的错误消息8152。|2958469|对于数据库兼容级别140或更低，错误消息2628仍然是一个选择加入错误消息，它要求启用跟踪标志460，并且此数据库范围的配置不起作用。|2958470|entityMap|0|LINK|mutability|MUTABLE|url|https://feedback.azure.com/forums/908035-sql-server/suggestions/32908417-binary-or-string-data-would-be-truncated-error|1|https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/|2|https://support.microsoft.com/en-us/help/4468101/optional-replacement-for-string-or-binary-data-would-be-truncated|3|https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15^0|0|F|0|0|1|1X|0|0|0|0|0|C|H|W|C|0|0|0|1|1C|0|0|0|0|23|1|0|0|0|0|1F|19|0|0|0|0|K|0|0|3P|2|0|0|0|0|0|0|0|0|0|0|1|1|3|0|0|0|15|0|0|0|0|2R|0|0|0|0^^$0|@$1|2|3|4|5|6|7|2L|8|@$9|2M|A|2N|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|2O|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|2P|8|@]|D|@$9|2Q|A|2R|1|2S]]|E|$]]|$1|J|3|-4|5|6|7|2T|8|@]|D|@]|E|$]]|$1|K|3|L|5|6|7|2U|8|@]|D|@]|E|$]]|$1|M|3|-4|5|6|7|2V|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|2W|8|@$9|2X|A|2Y|B|P]|$9|2Z|A|30|B|P]]|D|@]|E|$]]|$1|Q|3|R|5|S|7|31|8|@]|D|@]|E|$T|U]]|$1|V|3|-4|5|6|7|32|8|@]|D|@]|E|$]]|$1|W|3|X|5|I|7|33|8|@$9|34|A|35|B|C]]|D|@]|E|$]]|$1|Y|3|-4|5|6|7|36|8|@]|D|@]|E|$]]|$1|Z|3|-4|5|6|7|37|8|@]|D|@]|E|$]]|$1|10|3|11|5|6|7|38|8|@]|D|@$9|39|A|3A|1|3B]]|E|$]]|$1|12|3|-4|5|6|7|3C|8|@]|D|@]|E|$]]|$1|13|3|14|5|I|7|3D|8|@]|D|@]|E|$]]|$1|15|3|-4|5|6|7|3E|8|@]|D|@]|E|$]]|$1|16|3|17|5|6|7|3F|8|@$9|3G|A|3H|B|C]]|D|@]|E|$]]|$1|18|3|-4|5|6|7|3I|8|@]|D|@]|E|$]]|$1|19|3|-4|5|6|7|3J|8|@]|D|@]|E|$]]|$1|1A|3|1B|5|6|7|3K|8|@$9|3L|A|3M|B|C]]|D|@]|E|$]]|$1|1C|3|1D|5|6|7|3N|8|@]|D|@$9|3O|A|3P|1|3Q]]|E|$]]|$1|1E|3|-4|5|6|7|3R|8|@]|D|@]|E|$]]|$1|1F|3|1G|5|I|7|3S|8|@]|D|@]|E|$]]|$1|1H|3|-4|5|6|7|3T|8|@]|D|@]|E|$]]|$1|1I|3|1J|5|6|7|3U|8|@]|D|@]|E|$]]|$1|1K|3|1L|5|6|7|3V|8|@]|D|@]|E|$]]|$1|1M|3|-4|5|6|7|3W|8|@]|D|@]|E|$]]|$1|1N|3|-4|5|6|7|3X|8|@]|D|@]|E|$]]|$1|1O|3|-4|5|6|7|3Y|8|@]|D|@]|E|$]]|$1|1P|3|-4|5|6|7|3Z|8|@]|D|@]|E|$]]|$1|1Q|3|1R|5|I|7|40|8|@]|D|@$9|41|A|42|1|43]]|E|$]]|$1|1S|3|-4|5|6|7|44|8|@]|D|@]|E|$]]|$1|1T|3|1U|5|6|7|45|8|@$9|46|A|47|B|C]]|D|@]|E|$]]|$1|1V|3|1W|5|6|7|48|8|@]|D|@]|E|$]]|$1|1X|3|1Y|5|6|7|49|8|@]|D|@]|E|$]]|$1|1Z|3|20|5|6|7|4A|8|@$9|4B|A|4C|B|P]]|D|@]|E|$]]|$1|21|3|22|5|6|7|4D|8|@]|D|@]|E|$]]|$1|23|3|24|5|6|7|4E|8|@]|D|@]|E|$]]|$1|25|3|26|5|6|7|4F|8|@]|D|@]|E|$]]|$1|27|3|-4|5|6|7|4G|8|@]|D|@]|E|$]]]|28|$29|$5|2A|2B|2C|E|$2D|2E]]|2F|$5|2A|2B|2C|E|$2D|2G]]|2H|$5|2A|2B|2C|E|$2D|2I]]|2J|$5|2A|2B|2C|E|$2D|2K]]]]

SQL Server 2019 will finally return more meaningful error message. 

<blockquote>
 <a href="https://feedback.azure.com/forums/908035-sql-server/suggestions/32908417-binary-or-string-data-would-be-truncated-error" rel="noreferrer">Binary or string data would be truncated => error message enhancments</a>
 
 if you have that error (in production), it's not obvious to see which column or row this error comes from, and how to locate it exactly. 
</blockquote>

To enable new behavior you need to use <code>DBCC TRACEON(460)</code>. New error text from <code>sys.messages</code>:

<pre><code>SELECT * FROM sys.messages WHERE message_id = 2628
</code></pre>

<blockquote>
 2628 – String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.
</blockquote>

<a href="https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/" rel="noreferrer">String or Binary data would be truncated: replacing the infamous error 8152</a>

<blockquote>
 This new message is also backported to SQL Server 2017 CU12 (and in an upcoming SQL Server 2016 SP2 CU), but not by default. You need to enable trace flag 460 to replace message ID 8152 with 2628, either at the session or server level.
 
 Note that for now, even in SQL Server 2019 CTP 2.0 the same trace flag 460 needs to be enabled. In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.
</blockquote>

<hr>

SQL Server 2017 CU12 also supports this feature.

<a href="https://support.microsoft.com/en-us/help/4468101/optional-replacement-for-string-or-binary-data-would-be-truncated" rel="noreferrer">Improvement: Optional replacement for "String or binary data would be truncated" message with extended information in SQL Server 2017</a>

<blockquote>
 This SQL Server 2017 update introduces an optional message that contains the following additional context information.

<pre><code>Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber
String or binary data would be truncated in table '%.*ls', column '%.*ls'.
Truncated value: '%.*ls'.
</code></pre>
 
 The new message ID is 2628. This message replaces message 8152 in any error output if trace flag 460 is enabled.
</blockquote>

<a href="https://dbfiddle.uk/?rdbms=sqlserver_2017&amp;fiddle=8e3d1e4ecb83e5c24b7f833d88766996" rel="noreferrer">db&lt;>fiddle demo</a>

<hr>

<blockquote>
 <a href="https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15" rel="noreferrer">ALTER DATABASE SCOPED CONFIGURATION </a>
 
 VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }
 
 APPLIES TO: SQL Server (Starting with SQL Server 2019 (15.x)) and Azure SQL Database
 
 Allows you to enable or disable the new String or binary data would be
 truncated error message. SQL Server 2019 (15.x) introduces a new, more
 specific error message (2628) for this scenario:
 
 <code>String or binary data would be truncated in table '%.*ls', column'%.*ls'. Truncated value: '%.*ls'.</code>
 
 When set to ON under database compatibility level 150, truncation
 errors raise the new error message 2628 to provide more context and
 simplify the troubleshooting process.
 
 When set to OFF under database compatibility level 150, truncation
 errors raise the previous error message 8152.
 
 For database compatibility level 140 or lower, error message 2628
 remains an opt-in error message that requires trace flag 460 to be
 enabled, and this database scoped configuration has no effect.
</blockquote>

blocks|key|2958479|text|我编写了一个有用的存储过程来帮助识别和解决使用INSERT+SELECT语句时的文本截断问题(字符串或二进制数据将被截断)。它仅比较CHAR、VARCHAR、NCHAR和NVARCHAR字段，并逐个字段返回评估结果，以防可能导致错误。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2958480|EXEC+dbo.GetFieldStringTruncate+SourceTableName,+TargetTableName|code-block|syntax|javascript|2958481|2958482|此存储过程用于解决执行INSERT+SELECT语句时的文本截断问题。|blockquote|2958483|2958484|此存储过程的操作取决于用户先前是否识别出有问题的INSERT语句。然后将源数据插入到全局临时表中。建议使用SELECT+INTO语句。|2958485|在SELECT语句的每个字段的别名中，必须使用与目标表的字段相同的名称。|2958486|2958487|函数代码：|offset|length|style|BOLD|2958488|DECLARE+@strSQL+nvarchar(1000)
IF+NOT+EXISTS+(SELECT+*+FROM+dbo.sysobjects+where+id+=+OBJECT_ID(N'[dbo].[GetFieldStringTruncate]'))
++++BEGIN
++++++++SET+@strSQL+=+'CREATE+PROCEDURE+[dbo].[GetFieldStringTruncate]+AS+RETURN'
++++++++EXEC+sys.sp_executesql+@strSQL
++++END

GO

SET+ANSI_NULLS+ON
GO
SET+QUOTED_IDENTIFIER+ON
GO

/*
------------------------------------------------------------------------------------------------------------------------
++++Description:++++
++++++++++++++++++++Syntax+
++++++++++++++++++++---------------
++++++++++++++++++++dbo.GetFieldStringTruncate(SourceTable,+TargetTable)
++++++++++++++++++++%2B---------------------------%2B-----------------------%2B
++++++++++++++++++++%7C+++SourceTableName+++++++++%7C+++VARCHAR(255)++++++++%7C
++++++++++++++++++++%2B---------------------------%2B-----------------------%2B
++++++++++++++++++++%7C+++TargetTableName+++++++++%7C+++VARCHAR(255)++++++++%7C
++++++++++++++++++++%2B---------------------------%2B-----------------------%2B

++++++++++++++++++++Arguments
++++++++++++++++++++---------------
++++++++++++++++++++SourceTableName
++++++++++++++++++++The+name+of+the+source+table.+It+should+be+a+temporary+table+using+double+charp+'##'.+E.g.+'##temp'

++++++++++++++++++++TargetTableName
++++++++++++++++++++The+name+of+the+target+table.+It+is+the+table+that+receives+the+data+used+in+the+INSERT+INTO+stament.

++++++++++++++++++++Return+Type
++++++++++++++++++++----------------
++++++++++++++++++++Returns+a+table+with+a+list+of+all+the+fields+with+the+type+defined+as+text+and+performs+an+evaluation+indicating+which+field+would+present+the+problem+of+string+truncation.

++++++++++++++++++++Remarks
++++++++++++++++++++----------------
++++++++++++++++++++This+stored+procedure+is+oriented+to+the+problem+of+text+truncation+when+an+INSERT+SELECT+statement+is+made.
++++++++++++++++++++The+operation+of+this+stored+procedure+depends+on+the+user+previously+identifying+the+INSERT+statement+with+the+problem.+Then+inserting+the+source+data+into+a+global+temporary+table.+The+SELECT+INTO+statement+is+recommended.
++++++++++++++++++++You+must+use+the+same+name+of+the+field+of+the+destination+table+in+the+alias+of+each+field+of+the+SELECT+statement.

++++++++++++++++++++Examples
++++++++++++++++++++====================================================================================================

++++++++++++++++++++--A.+Test+basic

++++++++++++++++++++++++IF+EXISTS+(SELECT+*+FROM+sys.objects++WHERE+OBJECT_ID+=+OBJECT_ID(N'[dbo].[tblDestino]')+AND+TYPE+IN+(N'U'))
++++++++++++++++++++++++++++DROP+TABLE+tblDestino

++++++++++++++++++++++++CREATE+TABLE+tblDestino
++++++++++++++++++++++++(
++++++++++++++++++++++++++++Id+INT+IDENTITY,
++++++++++++++++++++++++++++Field1+VARCHAR(10),
++++++++++++++++++++++++++++Field2+VARCHAR(12),
++++++++++++++++++++++++++++Field3+VARCHAR(11),
++++++++++++++++++++++++++++Field4+VARCHAR(16),
++++++++++++++++++++++++++++Field5+VARCHAR(5),
++++++++++++++++++++++++++++Field6+VARCHAR(1),
++++++++++++++++++++++++++++Field7+VARCHAR(1),
++++++++++++++++++++++++++++Field8+VARCHAR(6),
++++++++++++++++++++++++++++Field9+VARCHAR(6),
++++++++++++++++++++++++++++Field10+VARCHAR(50),
++++++++++++++++++++++++++++Field11+VARCHAR(50),
++++++++++++++++++++++++++++Field12+VARCHAR(50)
++++++++++++++++++++++++)

++++++++++++++++++++++++INSERT+INTO+dbo.tblDestino
++++++++++++++++++++++++(
+++++++++++++++++++++++++++++Field1+,
+++++++++++++++++++++++++++++Field2+,
+++++++++++++++++++++++++++++Field3+,
+++++++++++++++++++++++++++++Field4+,
+++++++++++++++++++++++++++++Field5+,
+++++++++++++++++++++++++++++Field6+,
+++++++++++++++++++++++++++++Field7+,
+++++++++++++++++++++++++++++Field8+,
+++++++++++++++++++++++++++++Field9+,
+++++++++++++++++++++++++++++Field10+,
+++++++++++++++++++++++++++++Field11+,
+++++++++++++++++++++++++++++Field12
++++++++++++++++++++++++++++)
++++++++++++++++++++++++SELECT+
+++++++++++++++++++++++++++++'123456789'+,+--+Field1+-+varchar(10)
+++++++++++++++++++++++++++++'123456789'+,+--+Field2+-+varchar(12)
+++++++++++++++++++++++++++++'123456789'+,+--+Field3+-+varchar(11)
+++++++++++++++++++++++++++++'123456789'+,+--+Field4+-+varchar(16)
+++++++++++++++++++++++++++++'123456789'+,+--+Field5+-+varchar(5)
+++++++++++++++++++++++++++++'123456789'+,+--+Field6+-+varchar(1)
+++++++++++++++++++++++++++++'123456789'+,+--+Field7+-+varchar(1)
+++++++++++++++++++++++++++++'123456789'+,+--+Field8+-+varchar(6)
+++++++++++++++++++++++++++++'123456789'+,+--+Field9+-+varchar(6)
+++++++++++++++++++++++++++++'123456789'+,+--+Field10+-+varchar(50)
+++++++++++++++++++++++++++++'123456789'+,+--+Field11+-+varchar(50)
+++++++++++++++++++++++++++++'123456789'++--+Field12+-+varchar(50)
++++++++++++++++++++++++GO++

++++++++++++++++++++Result:
++++++++++++++++++++++++String+or+binary+data+would+be+truncated


++++++++++++++++++++*Here+you+get+the+truncation+error.+Then,+we+proceed+to+save+the+information+in+a+global+temporary+table.+
++++++++++++++++++++*IMPORTANT+REMINDER:+You+must+use+the+same+name+of+the+field+of+the+destination+table+in+the+alias+of+each+field+of+the+SELECT+statement.


++++++++++++++++++++Process:

++++++++++++++++++++++++IF+OBJECT_ID('tempdb..##TEMP')+IS+NOT+NULL+DROP+TABLE+##TEMP
++++++++++++++++++++++++go
++++++++++++++++++++++++SELECT+
+++++++++++++++++++++++++++++[Field1]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field2]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field3]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field4]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field5]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field6]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field7]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field8]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field9]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field10]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field11]+=+'123456789'+,
+++++++++++++++++++++++++++++[Field12]+=+'123456789'++
++++++++++++++++++++++++INTO+##TEMP

++++++++++++++++++++Result:
++++++++++++++++++++(1+row(s)+affected)

++++++++++++++++++++Test:
++++++++++++++++++++++++EXEC+dbo.GetFieldStringTruncate+@SourceTableName+=+'##TEMP',+@TargetTableName+=+'tblDestino'

++++++++++++++++++++Result:

++++++++++++++++++++++++(12+row(s)+affected)
++++++++++++++++++++++++ORIGEN+Nombre+Campo++++++++ORIGEN+Maximo+Largo++DESTINO+Nombre+Campo+++++DESTINO+Tipo+de+campo+++Evaluación
++++++++++++++++++++++++--------------------------+--------------------+------------------------+-----------------------+-------------------------
++++++++++++++++++++++++Field1+++++++++++++++++++++9++++++++++++++++++++02+-+Field1++++++++++++++VARCHAR(10)+++++++++++++
++++++++++++++++++++++++Field2+++++++++++++++++++++9++++++++++++++++++++03+-+Field2++++++++++++++VARCHAR(12)+++++++++++++
++++++++++++++++++++++++Field3+++++++++++++++++++++9++++++++++++++++++++04+-+Field3++++++++++++++VARCHAR(11)+++++++++++++
++++++++++++++++++++++++Field4+++++++++++++++++++++9++++++++++++++++++++05+-+Field4++++++++++++++VARCHAR(16)+++++++++++++
++++++++++++++++++++++++Field5+++++++++++++++++++++9++++++++++++++++++++06+-+Field5++++++++++++++VARCHAR(5)++++++++++++++possible+field+with+error
++++++++++++++++++++++++Field6+++++++++++++++++++++9++++++++++++++++++++07+-+Field6++++++++++++++VARCHAR(1)++++++++++++++possible+field+with+error
++++++++++++++++++++++++Field7+++++++++++++++++++++9++++++++++++++++++++08+-+Field7++++++++++++++VARCHAR(1)++++++++++++++possible+field+with+error
++++++++++++++++++++++++Field8+++++++++++++++++++++9++++++++++++++++++++09+-+Field8++++++++++++++VARCHAR(6)++++++++++++++possible+field+with+error
++++++++++++++++++++++++Field9+++++++++++++++++++++9++++++++++++++++++++10+-+Field9++++++++++++++VARCHAR(6)++++++++++++++possible+field+with+error
++++++++++++++++++++++++Field10++++++++++++++++++++9++++++++++++++++++++11+-+Field10+++++++++++++VARCHAR(50)+++++++++++++
++++++++++++++++++++++++Field11++++++++++++++++++++9++++++++++++++++++++12+-+Field11+++++++++++++VARCHAR(50)+++++++++++++
++++++++++++++++++++++++Field12++++++++++++++++++++9++++++++++++++++++++13+-+Field12+++++++++++++VARCHAR(50)+++++++++++++

++++++++++++++++++++====================================================================================================

++++------------------------------------------------------------------------------------------------------------

++++Responsible:++++Javier+Pardo+
++++Date:+++++++++++October+19/2018
++++WB+tests:+++++++Javier+Pardo+

++++------------------------------------------------------------------------------------------------------------

*/

ALTER+PROCEDURE+dbo.GetFieldStringTruncate
(
++++@SourceTableName+AS+VARCHAR(255)
++++,+@TargetTableName+AS+VARCHAR(255)
)
AS
BEGIN
++++BEGIN+TRY

++++++++DECLARE+@colsUnpivot+AS+NVARCHAR(MAX),
++++++++++++@colsUnpivotConverted+AS+NVARCHAR(MAX),
+++++++++++@query++AS+NVARCHAR(MAX)

++++++++SELECT+@colsUnpivot+=+stuff((
++++++++++++++++++++SELECT+DISTINCT+','+%2B+QUOTENAME(col.NAME)
++++++++++++++++++++FROM+tempdb.sys.tables+tab
++++++++++++++++++++INNER+JOIN+tempdb.sys.columns+col
++++++++++++++++++++++++ON+col.object_id+=+tab.object_id
++++++++++++++++++++INNER+JOIN+tempdb.sys.types+typ
++++++++++++++++++++++++ON+col.system_type_id+=+TYP.system_type_id
++++++++++++++++++++WHERE+tab.NAME+=+@SourceTableName
++++++++++++++++++++FOR+XML+path('')
++++++++++++++++++++),+1,+1,+'')
++++++++++++++++,@colsUnpivotConverted+=+stuff((
++++++++++++++++++++SELECT+DISTINCT+','+%2B+'CONVERT(VARCHAR(MAX),'+%2B+QUOTENAME(col.NAME)+%2B+')+AS+'+%2B+QUOTENAME(col.NAME)
++++++++++++++++++++FROM+tempdb.sys.tables+tab
++++++++++++++++++++INNER+JOIN+tempdb.sys.columns+col
++++++++++++++++++++++++ON+col.object_id+=+tab.object_id
++++++++++++++++++++INNER+JOIN+tempdb.sys.types+typ
++++++++++++++++++++++++ON+col.system_type_id+=+TYP.system_type_id
++++++++++++++++++++WHERE+tab.NAME+=+@SourceTableName
++++++++++++++++++++FOR+XML+path('')
++++++++++++++++++++),+1,+1,+'')


++++++++--https://stackoverflow.com/questions/11158017/column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list
++++++++IF+OBJECT_ID('tempdb..##TablaConMaximos')+IS+NOT+NULL+DROP+TABLE+##TablaConMaximos

++++++++set+@query+
++++++++++=+'SELECT+u.d+AS+colname,+MAX(LEN(u.data))+as+[maximo_largo]
++++++++++++INTO+##TablaConMaximos
++++++++++++FROM+
++++++++++++(
++++++++++++++++SELECT+'+%2B+@colsUnpivotConverted+%2B+'
++++++++++++++++FROM+'+%2B+@SourceTableName+%2B+'
++++++++++++)+T
++++++++++++UNPIVOT
+++++++++++++(
++++++++++++++++data
++++++++++++++++for+d+in+('%2B+@colsunpivot+%2B')
+++++++++++++)+u
+++++++++++++GROUP+BY+u.d'

++++++++PRINT+@query

++++++++exec+sp_executesql+@query;

++++++++------------------------------------------------------------------------------------------------------------
++++++++SELECT+--'Nombre+de+campo'+=+RIGHT('00'+%2B+ISNULL(CONVERT(VARCHAR,col.column_id),''),2)+%2B+'+-+'+%2B+col.name+%2B+'+'
++++++++++++--,+'Tipo+de+campo'+=+ISNULL(CONVERT(VARCHAR,upper(typ.name)),'')+%2B+'('+%2B+ISNULL(CONVERT(VARCHAR,col.max_length),'')+%2B+')'
++++++++++++[ORIGEN+Nombre+Campo]+=+tcm.colname
++++++++++++,+[ORIGEN+Maximo+Largo]+=+tcm.maximo_largo
++++++++++++,+[DESTINO+Nombre+Campo]+=+DESTINO.[Nombre+de+campo]
++++++++++++,+[DESTINO+Tipo+de+campo]+=+DESTINO.[Tipo+de+campo]
++++++++++++,+[Evaluación]+=+CASE+WHEN+DESTINO.maximo_largo+<+tcm.maximo_largo+THEN+'possible+field+with+error'+ELSE+''+END
++++++++++++--,+*+
++++++++FROM+tempdb.sys.tables+tab
++++++++++++INNER+JOIN+tempdb.sys.columns+col
++++++++++++++++ON+col.object_id+=+tab.object_id
++++++++++++INNER+JOIN+tempdb.sys.types+typ
++++++++++++++++ON+col.system_type_id+=+TYP.system_type_id
++++++++++++RIGHT+JOIN+
++++++++++++++++(
++++++++++++++++++++SELECT+column_id
++++++++++++++++++++++++,+[Nombre+de+campo]+=+RIGHT('00'+%2B+ISNULL(CONVERT(VARCHAR,col.column_id),''),2)+%2B+'+-+'+%2B+col.name+%2B+'+'
++++++++++++++++++++++++,+[Tipo+de+campo]+=+ISNULL(CONVERT(VARCHAR,upper(typ.name)),'')+%2B+'('+%2B+ISNULL(CONVERT(VARCHAR,col.max_length),'')+%2B+')'
++++++++++++++++++++++++,+[maximo_largo]+=+col.max_length
++++++++++++++++++++++++,+[colname]+=+col.name
++++++++++++++++++++FROM+sys.tables+tab
++++++++++++++++++++++++INNER+JOIN+sys.columns+col
++++++++++++++++++++++++++++ON+col.object_id+=+tab.object_id
++++++++++++++++++++++++INNER+JOIN+sys.types+typ
++++++++++++++++++++++++++++ON+col.system_type_id+=+TYP.system_type_id
++++++++++++++++++++WHERE+tab.NAME+=+@TargetTableName
++++++++++++++++)+AS+DESTINO
++++++++++++++++++++ON+col.name+=+DESTINO.colname
++++++++++++INNER+JOIN+##TablaConMaximos+tcm
++++++++++++++++ON+tcm.colname+=+DESTINO.colname

++++++++WHERE+tab.NAME+=+@SourceTableName
++++++++++++AND+typ.name+LIKE+'%25char%25'
++++++++ORDER+BY+col.column_id

++++END+TRY
++++BEGIN+CATCH
++++++++SELECT+'Internal+error+ocurred'+AS+Message
++++END+CATCH+++

END|2958489|目前仅支持NVARCHAR+CHAR、、NCHAR和NVARCHAR数据类型。你可以在下面的链接中找到这个代码的最新版本，我们互相帮助来改进它。GetFieldStringTruncate.sql|2958490|https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d|2958491|entityMap|0|LINK|mutability|MUTABLE|url^0|0|0|0|0|0|0|0|0|0|5|0|0|J|F|0|0|1U|0|0^^$0|@$1|2|3|4|5|6|7|19|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1A|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|H|3|I|5|J|7|1C|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|1D|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1E|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1F|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1H|8|@$S|1I|T|1J|U|V]]|9|@]|A|$]]|$1|W|3|X|5|D|7|1K|8|@]|9|@]|A|$E|F]]|$1|Y|3|Z|5|6|7|1L|8|@$S|1M|T|1N|U|V]]|9|@]|A|$]]|$1|10|3|11|5|6|7|1O|8|@]|9|@$S|1P|T|1Q|1|1R]]|A|$]]|$1|12|3|-4|5|6|7|1S|8|@]|9|@]|A|$]]]|13|$14|$5|15|16|17|A|$18|11]]]]

I wrote a useful store procedure to help identify and resolve the problem of text truncation (String or binary data would be truncated) when the INSERT SELECT statement is used. It compares fields CHAR, VARCHAR, NCHAR AND NVARCHAR only and returns an evaluation field by field in case of being the possible cause of the error. 

<pre><code>EXEC dbo.GetFieldStringTruncate SourceTableName, TargetTableName
</code></pre>

<blockquote>
 This stored procedure is oriented to the problem of text truncation when an INSERT SELECT statement is made.
 
 The operation of this stored procedure depends on the user previously identifying the INSERT statement with the problem. Then inserting the source data into a global temporary table. The SELECT INTO statement is recommended.
 
 You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.
</blockquote>

FUNCTION CODE:

<pre><code>DECLARE @strSQL nvarchar(1000)
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = OBJECT_ID(N'[dbo].[GetFieldStringTruncate]'))
 BEGIN
 SET @strSQL = 'CREATE PROCEDURE [dbo].[GetFieldStringTruncate] AS RETURN'
 EXEC sys.sp_executesql @strSQL
 END

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
------------------------------------------------------------------------------------------------------------------------
 Description: 
 Syntax 
 ---------------
 dbo.GetFieldStringTruncate(SourceTable, TargetTable)
 +---------------------------+-----------------------+
 | SourceTableName | VARCHAR(255) |
 +---------------------------+-----------------------+
 | TargetTableName | VARCHAR(255) |
 +---------------------------+-----------------------+

 Arguments
 ---------------
 SourceTableName
 The name of the source table. It should be a temporary table using double charp '##'. E.g. '##temp'

 TargetTableName
 The name of the target table. It is the table that receives the data used in the INSERT INTO stament.

 Return Type
 ----------------
 Returns a table with a list of all the fields with the type defined as text and performs an evaluation indicating which field would present the problem of string truncation.

 Remarks
 ----------------
 This stored procedure is oriented to the problem of text truncation when an INSERT SELECT statement is made.
 The operation of this stored procedure depends on the user previously identifying the INSERT statement with the problem. Then inserting the source data into a global temporary table. The SELECT INTO statement is recommended.
 You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.

 Examples
 ====================================================================================================

 --A. Test basic

 IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[tblDestino]') AND TYPE IN (N'U'))
 DROP TABLE tblDestino

 CREATE TABLE tblDestino
 (
 Id INT IDENTITY,
 Field1 VARCHAR(10),
 Field2 VARCHAR(12),
 Field3 VARCHAR(11),
 Field4 VARCHAR(16),
 Field5 VARCHAR(5),
 Field6 VARCHAR(1),
 Field7 VARCHAR(1),
 Field8 VARCHAR(6),
 Field9 VARCHAR(6),
 Field10 VARCHAR(50),
 Field11 VARCHAR(50),
 Field12 VARCHAR(50)
 )

 INSERT INTO dbo.tblDestino
 (
 Field1 ,
 Field2 ,
 Field3 ,
 Field4 ,
 Field5 ,
 Field6 ,
 Field7 ,
 Field8 ,
 Field9 ,
 Field10 ,
 Field11 ,
 Field12
 )
 SELECT 
 '123456789' , -- Field1 - varchar(10)
 '123456789' , -- Field2 - varchar(12)
 '123456789' , -- Field3 - varchar(11)
 '123456789' , -- Field4 - varchar(16)
 '123456789' , -- Field5 - varchar(5)
 '123456789' , -- Field6 - varchar(1)
 '123456789' , -- Field7 - varchar(1)
 '123456789' , -- Field8 - varchar(6)
 '123456789' , -- Field9 - varchar(6)
 '123456789' , -- Field10 - varchar(50)
 '123456789' , -- Field11 - varchar(50)
 '123456789' -- Field12 - varchar(50)
 GO 

 Result:
 String or binary data would be truncated


 *Here you get the truncation error. Then, we proceed to save the information in a global temporary table. 
 *IMPORTANT REMINDER: You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.


 Process:

 IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE ##TEMP
 go
 SELECT 
 [Field1] = '123456789' ,
 [Field2] = '123456789' ,
 [Field3] = '123456789' ,
 [Field4] = '123456789' ,
 [Field5] = '123456789' ,
 [Field6] = '123456789' ,
 [Field7] = '123456789' ,
 [Field8] = '123456789' ,
 [Field9] = '123456789' ,
 [Field10] = '123456789' ,
 [Field11] = '123456789' ,
 [Field12] = '123456789' 
 INTO ##TEMP

 Result:
 (1 row(s) affected)

 Test:
 EXEC dbo.GetFieldStringTruncate @SourceTableName = '##TEMP', @TargetTableName = 'tblDestino'

 Result:

 (12 row(s) affected)
 ORIGEN Nombre Campo ORIGEN Maximo Largo DESTINO Nombre Campo DESTINO Tipo de campo Evaluación
 -------------------------- -------------------- ------------------------ ----------------------- -------------------------
 Field1 9 02 - Field1 VARCHAR(10) 
 Field2 9 03 - Field2 VARCHAR(12) 
 Field3 9 04 - Field3 VARCHAR(11) 
 Field4 9 05 - Field4 VARCHAR(16) 
 Field5 9 06 - Field5 VARCHAR(5) possible field with error
 Field6 9 07 - Field6 VARCHAR(1) possible field with error
 Field7 9 08 - Field7 VARCHAR(1) possible field with error
 Field8 9 09 - Field8 VARCHAR(6) possible field with error
 Field9 9 10 - Field9 VARCHAR(6) possible field with error
 Field10 9 11 - Field10 VARCHAR(50) 
 Field11 9 12 - Field11 VARCHAR(50) 
 Field12 9 13 - Field12 VARCHAR(50) 

 ====================================================================================================

 ------------------------------------------------------------------------------------------------------------

 Responsible: Javier Pardo 
 Date: October 19/2018
 WB tests: Javier Pardo 

 ------------------------------------------------------------------------------------------------------------

*/

ALTER PROCEDURE dbo.GetFieldStringTruncate
(
 @SourceTableName AS VARCHAR(255)
 , @TargetTableName AS VARCHAR(255)
)
AS
BEGIN
 BEGIN TRY

 DECLARE @colsUnpivot AS NVARCHAR(MAX),
 @colsUnpivotConverted AS NVARCHAR(MAX),
 @query AS NVARCHAR(MAX)

 SELECT @colsUnpivot = stuff((
 SELECT DISTINCT ',' + QUOTENAME(col.NAME)
 FROM tempdb.sys.tables tab
 INNER JOIN tempdb.sys.columns col
 ON col.object_id = tab.object_id
 INNER JOIN tempdb.sys.types typ
 ON col.system_type_id = TYP.system_type_id
 WHERE tab.NAME = @SourceTableName
 FOR XML path('')
 ), 1, 1, '')
 ,@colsUnpivotConverted = stuff((
 SELECT DISTINCT ',' + 'CONVERT(VARCHAR(MAX),' + QUOTENAME(col.NAME) + ') AS ' + QUOTENAME(col.NAME)
 FROM tempdb.sys.tables tab
 INNER JOIN tempdb.sys.columns col
 ON col.object_id = tab.object_id
 INNER JOIN tempdb.sys.types typ
 ON col.system_type_id = TYP.system_type_id
 WHERE tab.NAME = @SourceTableName
 FOR XML path('')
 ), 1, 1, '')


 --https://stackoverflow.com/questions/11158017/column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list
 IF OBJECT_ID('tempdb..##TablaConMaximos') IS NOT NULL DROP TABLE ##TablaConMaximos

 set @query 
 = 'SELECT u.d AS colname, MAX(LEN(u.data)) as [maximo_largo]
 INTO ##TablaConMaximos
 FROM 
 (
 SELECT ' + @colsUnpivotConverted + '
 FROM ' + @SourceTableName + '
 ) T
 UNPIVOT
 (
 data
 for d in ('+ @colsunpivot +')
 ) u
 GROUP BY u.d'

 PRINT @query

 exec sp_executesql @query;

 ------------------------------------------------------------------------------------------------------------
 SELECT --'Nombre de campo' = RIGHT('00' + ISNULL(CONVERT(VARCHAR,col.column_id),''),2) + ' - ' + col.name + ' '
 --, 'Tipo de campo' = ISNULL(CONVERT(VARCHAR,upper(typ.name)),'') + '(' + ISNULL(CONVERT(VARCHAR,col.max_length),'') + ')'
 [ORIGEN Nombre Campo] = tcm.colname
 , [ORIGEN Maximo Largo] = tcm.maximo_largo
 , [DESTINO Nombre Campo] = DESTINO.[Nombre de campo]
 , [DESTINO Tipo de campo] = DESTINO.[Tipo de campo]
 , [Evaluación] = CASE WHEN DESTINO.maximo_largo &lt; tcm.maximo_largo THEN 'possible field with error' ELSE '' END
 --, * 
 FROM tempdb.sys.tables tab
 INNER JOIN tempdb.sys.columns col
 ON col.object_id = tab.object_id
 INNER JOIN tempdb.sys.types typ
 ON col.system_type_id = TYP.system_type_id
 RIGHT JOIN 
 (
 SELECT column_id
 , [Nombre de campo] = RIGHT('00' + ISNULL(CONVERT(VARCHAR,col.column_id),''),2) + ' - ' + col.name + ' '
 , [Tipo de campo] = ISNULL(CONVERT(VARCHAR,upper(typ.name)),'') + '(' + ISNULL(CONVERT(VARCHAR,col.max_length),'') + ')'
 , [maximo_largo] = col.max_length
 , [colname] = col.name
 FROM sys.tables tab
 INNER JOIN sys.columns col
 ON col.object_id = tab.object_id
 INNER JOIN sys.types typ
 ON col.system_type_id = TYP.system_type_id
 WHERE tab.NAME = @TargetTableName
 ) AS DESTINO
 ON col.name = DESTINO.colname
 INNER JOIN ##TablaConMaximos tcm
 ON tcm.colname = DESTINO.colname

 WHERE tab.NAME = @SourceTableName
 AND typ.name LIKE '%char%'
 ORDER BY col.column_id

 END TRY
 BEGIN CATCH
 SELECT 'Internal error ocurred' AS Message
 END CATCH 

END
</code></pre>

For now only supports the data types CHAR, VARCHAR, NCHAR and NVARCHAR. You can find the last versión of this code in the next link below and we help each other to improve it. GetFieldStringTruncate.sql 

<a href="https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d" rel="nofollow noreferrer">https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d</a>

blocks|key|262531|text|如果您使用的是SQL+Server+2016-2017:要修复此问题，请打开跟踪标志460|type|unstyled|depth|inlineStyleRanges|entityRanges|data|262532|DBCC+TRACEON(460,+1);
GO|code-block|syntax|javascript|262533|并确保在以下情况下将其关闭：|262534|DBCC+TRACEOFF(460,+1);
GO|262535|source|offset|length|262536|entityMap|0|LINK|mutability|MUTABLE|url|https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/^0|0|0|0|0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@$M|11|N|12|1|13]]|A|$]]|$1|O|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|P|$Q|$5|R|S|T|A|$U|V]]]]

If you’re on SQL Server 2016-2017:
to fix it, turn on trace flag 460

<pre><code>DBCC TRACEON(460, 1);
GO
</code></pre>

and make sure you turn it off after:

<pre><code>DBCC TRACEOFF(460, 1);
GO
</code></pre>

<a href="https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/" rel="nofollow noreferrer">source</a>

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

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

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.

SQL Server String or binary data would be truncated

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我参与了一个数据迁移项目。当我尝试将一个表中的数据插入另一个表(SQL Server 2005)时，出现以下错误：消息8152，级别16，州13，第1行字符串或二进制数据将被截断。源数据列与数据类型匹配，并且在目标表列的长度定义范围内，因此我不知道是什么导致了这个错误。

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

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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