Saturday, 12 January 2013

SQL String or Binary Data Would be Truncated. The Statement has been Terminated

One day I am trying to insert data into one table using SQL queries at that time I got error like “String or binary data would be truncated. The statement has been terminated.” Actually this problem because of I declared column datatype varchar(25) but I am inserting data more than 25 characters in that column. To solve this problem I modified column datatype varcha (25) to varchar(50)

I will explain with one example I have query like this
DECLARE @UserDetails TABLE(UserId INT, UserName VARCHAR(25),Designation VARCHAR(10))
INSERT INTO @UserDetails(UserId,UserName,Designation)
VALUES(1,'Suresh Dasari','Senior Software Engineer')
SELECT * FROM @UserDetails
If you observe above query I declared Designation field with VARCHAR(10) and inserting more than 10 characters because of that I got error like
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.
(0 row(s) affected)
To solve this problem I changed Designation datatype VARCHAR(10) to VARCHAR(50) and run the below query  
DECLARE @UserDetails TABLE(UserId INT, UserName VARCHAR(25),Designation VARCHAR(50))
INSERT INTO @UserDetails(UserId,UserName,Designation)
VALUES(1,'Suresh Dasari','Senior Software Engineer')
SELECT * FROM @UserDetails
Once I run above query I got output like this
Happy Coding………

No comments:

Post a Comment