Microsoft - Microsoft SQL Server - SQL - Insert - Truncated Insert

Create a test database

CREATE DATABASE TestDB
GO
USE TestDB
GO
CREATE TABLE TestTable
(
 [ID] INT IDENTITY(1,1),
 [NAME] VARCHAR(10),
)
GO

INSERT INTO TestTable VALUES ('Some dummy data')
GO
INSERT INTO TestTable VALUES ('Some more dummy data')
GO

returns errors:

SQL TRUNCATE error message ‘String OR BINARY DATA would be truncated.’

Check the length of the string that is being inserted

SELECT len('Some dummy data') AS [StringLength]
 
SELECT len('Some more dummy data') AS [StringLength]

NOTE: This should return 15 and 20.

These are both longer than the length of the NAME field which caters for 10 characters.

Check the length of the Name column

SELECT character_maximum_length,column_name
FROM information_schema.columns
WHERE TABLE_NAME = 'TestTable'
AND Column_name='NAME'

returns:

Solution

SET ANSI_WARNINGS off
GO
 
INSERT INTO TestTable VALUES ('Some dummy data')
GO
INSERT INTO TestTable VALUES ('Some more dummy data')
GO
 
SET ANSI_WARNINGS ON
GO

Wiki

Table of Contents

Microsoft - Microsoft SQL Server - SQL - Insert - Truncated Insert

Create a test database

Check the length of the string that is being inserted

Check the length of the Name column

Solution