How to fix 'string or binary data would be truncated' in sql

How to fix 'string or binary data would be truncated' in SQL

For example:

CREATE TABLE Customers (
 id INT,
 CustomerName NVARCHAR(10),
 Sex VARCHAR(8)
);
insert into Customers(id, CustomerName, Sex) values (1, 'Lucy', 'Female');

In the above sql command, the CustomerName column is defined to store strings up to 10 characters. However, an attempt was made to insert a string of 15 characters, which exceeds this limit. As a result, SQL Server returns the error "string or binary data would be truncated."

How to locate the column causing the error

To get precise truncation details in SQL Server 2016/17, you should enable trace flag 460. This flag allows SQL Server to specify which column is causing the truncation error. You can enable trace flag 460 by running the following query when adding or updating records in a table.

insert into Customers(id, CustomerName, Sex) values (1, 'Tom Makute La Fox Ka', 'Male')
OPTION (QUERYTRACEON 460);

For SQL Server 2019 upgraded from a previous version, you can run the following query in the database to receive a detailed truncation warning.

ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

The first option is to ensure that the string value being inserted fits within the defined size of the column.

To insert data that exceeds the current column size, you need to increase the column length using the ALTER TABLE command. For example, if the column length needs to be expanded, you can adjust it with this command.

ALTER TABLE Customers
ALTER COLUMN CustomerName NVARCHAR(20)

If increasing the column size is not an option, you can truncate or shorten the data before inserting it.

insert into Customers(id, CustomerName, Sex) values (1, LEFT('A very long string...', 20), 'Male');

By following these steps, you can address and resolve issues related to data truncation in SQL.