String or binary data would be truncated

Question

1.00/5 (1 vote)

See more:

I Am executing this query but getting error ..

C#

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 :

C#

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

Posted 20-Mar-15 0:21am

Member 11337367

Updated 20-Mar-15 0:34am

Thanks7872

v3

Add a Solution

Comments

Thanks7872 20-Mar-15 6:34am

Its means, you are inserting more data than it can handle. In very simple words, you have declared some column as varchar(10) and storing data with length more than 10.

Saral S Stalin 20-Mar-15 6:34am

Can you post the schema of tbl_item_rate and r2

Member 11337367 20-Mar-15 6:45am

CREATE TABLE [dbo].[tbl_item_rate](
[c_Item_code] [varchar](10) NOT NULL,
[n_rate] [numeric](18, 2) NULL,
[n_mrp] [numeric](18, 2) NULL,
[n_std_rate] [numeric](18, 2) NULL,
[n_pts_rate] [numeric](18, 2) NULL,
[d_date_from] [datetime] NOT NULL,
[d_date_to] [datetime] NULL,
CONSTRAINT [PK_tbl_item_rate] PRIMARY KEY CLUSTERED
(
[c_Item_code] ASC,
[d_date_from] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[r2](
[itemcode] [varchar](50) NULL,
[itemname] [varchar](50) NULL,
[shortname] [varchar](50) NULL,
[itemtype] [float] NULL,
[companyname] [varchar](50) NULL,
[description] [varchar](150) NULL,
[pack] [float] NULL,
[pricetoretailer] [float] NULL,
[mrp] [float] NULL,
[standardrate] [float] NULL,
[pricetostockiest] [float] NULL,
[brandcode] [nvarchar](255) NULL,
[categorycode] [nvarchar](255) NULL,
[divisioncode] [varchar](50) NULL,
[groupcode] [nvarchar](255) NULL,
[nrvrate] [nvarchar](255) NULL,
[manufacturercode] [nvarchar](255) NULL,
[qtyperstrip] [nvarchar](255) NULL
) ON [PRIMARY]

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Saral S Stalin · Answer 1 · 2015-03-20T00:52:00

Solution 3

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

SQL

convert(varchar(50),itemcode)

to

SQL

convert(varchar(10),itemcode)

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

Posted 20-Mar-15 0:52am

Saral S Stalin

Updated 20-Mar-15 1:05am

v3

Comments

[no name] 20-Mar-15 6:58am

"Either you increase the size of c_item_code to varchar(50) (best thing)"

You should be cautious with that kind of advice. You don't know if he's "playing around" with his own local DB or if he works on something bigger, potentially in a company. A column length restriction might be in place for a very good reason and the proper solution might be to restrict the input length.

Saral S Stalin 20-Mar-15 7:01am

Noted your point. Thanks for the comment.

[no name] 20-Mar-15 7:07am

You're welcome.

Abhinav S · Answer 2 · 2015-03-20T00:43:00

Solution 1

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.

Posted 20-Mar-15 0:43am

Abhinav S

OriginalGriff · Answer 3 · 2015-03-20T00:46:00

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:

SQL

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

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.