Table of Contents

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:

10

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