String or binary data would be truncated the statement has been terminated

Description

This SQL error occurs when database field’s datatype,size does not match with the inserting or updating value. Suppose we have declared a SQL column field named username varchar[10] and when we trying to inserting username of more than 20 characters then we will get an error like “String or binary data would be truncated the statement has been terminated“. So in that case, we have to increase datatype length 20 or decrease length of username value before insertion to avoid the error “String or binary data would be truncated“.

Example:

DECLARE @UserData TABLE(UserId INT, UserName VARCHAR(10))
INSERT INTO @UserData VALUES(1,'James Anderson')
SELECT * FROM @UserData

From above query, it clearly indicates we have declared datatype VARCHAR(10) for UserName. Now we can insert usernames only with 10 characters or less. but here, we are trying to insert the user ‘James Anderson’ it has more than 10 characters. so we should get the following error when execute above statement.

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

(0 row(s) affected)

Fix error String or binary data would be truncated:

To fix this sql error we need to increase the UserName column’s datatype size from VARCHAR(10) to VARCHAR(20)

DECLARE @UserData TABLE(UserId INT, UserName VARCHAR(20))
INSERT INTO @UserData VALUES(1,'James Anderson')
SELECT * FROM @UserData