字符串或二进制数据将被截断 [英] String or binary data would be truncated

问题描述

我正在执行此查询但收到错误..

I Am executing this query but getting error ..

insert into tbl_item_rate
select convert(varchar(50),itemcode),convert(numeric(18,2),pricetoretailer),
convert(numeric(18,2),mrp),convert(numeric(18,2),standardrate),
convert(numeric(18,2),pricetostockiest),convert(datetime,'2015/01/01',103),null
from r2

错误：

Error :

String or binary data would be truncated.
The statement has been terminated.

推荐答案

正如您提供的模式一样，问题现在非常明确。



tbl_item_rate第一列是[c_Item_code] [varchar]（10）NOT NULL，你正在向它插入一个varchar（50）列r2.ItemCode。



要么将c_item_code的大小增加到varchar（50），要么将其写入更改

As you have provided the schema's the problem is very clear now.

tbl_item_rate first column is [c_Item_code] [varchar](10) NOT NULL, you are inserting a varchar(50) column r2.ItemCode to it.

Either you increase the size of c_item_code to varchar(50) or as the way you write change

convert(varchar(50),itemcode)

到

convert(varchar(10),itemcode)

请注意，这会导致商品代码中出现的值截断，但代码不会失败：）

Note that this will cause a truncation in value which is present in itemcode but your code wont fail :)

SQL SERVER - 使用存储过程将'截断'字符串或二进制数据的快速解决方案 [ ^ ]可以帮助您找出创建此错误的确切列。

SQL SERVER – A quick solution to ‘String or binary data would be truncated’ using Stored procedure[^] can help you figure out the exact column that is creating this error.

查看您的数据库，以及您声明的列的大小 - 例如NVARCHAR列dafualt到a最多50个字符，因此如果您尝试向列中插入多个字符，它将失败，因为整个文本将不适合：因此字符串或二进制数据将被截断。 SQL不会在不告诉你的情况下丢弃数据......



将INSERT语句中的列命名为INSERT语句也是一个非常好的主意：

Look at your database, and the size of the columns you declared - NVARCHAR columns for example dafualt to a maximum of 50 characters, so if you try to insert more than that into the column, it will fail because the whole text will not fit: Hence "String or binary data would be truncated." SQL will not throw away data without telling you...

It's also a very good idea to name the columns you are inserting into as part of your INSERT statement:

INSERT INTO MyTable (Column1Name, Column2Name) VALUES ('value for C1', 'value for c2')

如果不这样做，那么SQL将以当前列顺序插入值，从最顶部/最左侧开始并按顺序进行。如果类型不合适，它将不会尝试跳过列，并且您的错误也可能与此相关。

If you don't, the SQL will insert the values in the current column order, starting with the top / left most and proceeding sequentially. It will not try to "skip" columns if the type is inappropriate, and your error may be related to that as well.

这篇关于字符串或二进制数据将被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！