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

Question

   Create Table rs
   (
   Id int  IDENTITY (1,1) Primary Key,
   mId int Not NUll,
   ad varchar Not NUll,
   stvarchar Not NUll,
   et varchar Not NUll,
   nt varchar(max)
   );


   insert into rs ( nt, et, st, ad, mId) 
values ('as','as','as','as',12)

I am getting this error

Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

It is a simple sql but finding difficulty in resolving it

Possible duplicate of : http://stackoverflow.com/questions/5591473/error-string-or-binary-data-would-be-truncated-when-trying-to-insert — Rambler, Jun 23 '16 at 10:42

score 3 · Answer 1 · edited Jun 23 '16 at 10:48

You are not setting size for your varchar variable so the size will default to 1, Hence you are getting this error when inserting varchar of size 2 into your table, Provide size for your varchar variables to resolve this issue

  Create Table rs
   (
   Id int  IDENTITY (1,1) Primary Key,
   mId int Not NUll,
   ad varchar(10) Not NUll,
   st varchar(10) Not NUll,
   et varchar(10) Not NUll,
   nt varchar(max)
   );

score 2 · Answer 2 · edited Jul 29 '20 at 18:26

From MSDN:

varchar [ ( n | max ) ]
Variable-length, non-Unicode string data. n defines the string length and can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).

Remarks When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

specify Default length for Varchar .also look at this article..

   Create Table rs
   (
   Id int  IDENTITY (1,1) Primary Key,
   mId int Not NUll,
   ad varchar(5) Not NUll,
   st varchar(5) Not NUll,
   et varchar(5) Not NUll,
   nt varchar(max)
   );

   insert into rs ( nt, et, st, ad, mId) 
   values ('as','as','as','as',12

score 2 · Accepted Answer · answered Jun 23 '16 at 10:41

As @Krish pointed out you need to allocate space .i.e define size for each column

     Create Table rs
   (
     Id int  IDENTITY (1,1) Primary Key,
     mId int Not NUll,
     ad varchar(2) Not NUll,
     st varchar(2) Not NUll,
     et varchar(2) Not NUll,
     nt varchar(max)
   );


   insert into rs ( nt, et, st, ad, mId) 
   values ('as','as','as','as',12)

OR

The default size is 1 so this should also work... but this is not what you want (it just to explain the problem)

INSERT INTO #rs ( mId, ad, st, et, nt) 
VALUES (12, 'a','a', 'a', 'a')

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

3 Answers3