[Resolved] MS SQL Error - String or binary data would be truncated sql

A Systematic Approach for Resolution

Problem

While executing insert statement in MS SQL, you may see the following error,

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

SQL Error Details

SQL Error Code: 8152

SQL Error Message: String or binary data would be truncated. The statement has been terminated.

Cause of the Error

The column that you are trying to insert a value in to, can not accept the new value as it is larger in size than the supported.

Example

CREATE TABLE [Data].[SyncLog](
 [SyncID] INT IDENTITY(1,1), 
 [SyncName] VARCHAR(16) NOT NULL,
 [SyncType] VARCHAR(8) NOT NULL,
 [SourceId] INT,
 [DestinationId] INT,
 [SyncTime] DATE,
 [SyncStatus] INT
)
INSERT INTO [Data].[SyncLog]([SyncName], [SyncType], [SyncStatus]) 
VALUES ("Billing Data", "BranchDetailsSync", 0);

Here the SyncType defined to be of VARCHAR that can hold 8 characters. But the insert statement is trying to insert a string of 17 characters in size.

Solution

Now you may have understood the issue & possible resolution.

But more important fact is you should see this in the requirement aspect rather than in technical. The resolution can be straight forward, but you should evaluate the requirement first and act accordingly.

If you have a limitation/ strict policy for the size of the column, maybe you are trying to insert an invalid value to that column. But if it is a mistake, just trim the value to fit the column.

INSERT INTO [Data].[SyncLog]([SyncName], [SyncType], [SyncStatus]) 
VALUES ("Billing Data", "BrDSync", 0);

But if you are sure about the value, maybe you have to ALTER TABLE to support lengthier values for the given column by ALTER COLUMN to increase the size.

ALTER TABLE [Data].[SyncLog]
ALTER COLUMN [SyncType] VARCHAR(36)

.....