Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft - Microsoft SQL Server - SQL - Insert - Truncated Insert ======

===== Create a test database =====

<code sql>
Create Database TestDB
Go
Use TestDB
Go
CREATE TABLE TestTable
(
 [ID] INT identity(1,1),
 [NAME] VARCHAR(10),
)
GO
</code>

----

<code sql>
INSERT INTO TestTable VALUES ('Some dummy data')
GO
INSERT INTO TestTable VALUES ('Some more dummy data')
GO
</code>

returns errors:

<code sql>
SQL truncate error message ‘String or binary data would be truncated.’ 
</code>

----

===== Check the length of the string that is being inserted =====

<code sql>	
select len('Some dummy data') as [StringLength]
 
Select len('Some more dummy data') as [StringLength]
</code>

<WRAP info>
**NOTE:**  This should return 15 and 20.

  * These are both longer than the length of the NAME field which caters for 10 characters.
</WRAP>

----

===== Check the length of the Name column =====

<code sql>
select character_maximum_length,column_name
from information_schema.columns
where table_name = 'TestTable'
and Column_name='NAME'
</code>

returns:

<code sql>
10
</code>

----

===== Solution =====

<code sql>
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
</code>