|
|
Error Message
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Causes:
This error is usually encountered when inserting a record in a table where one of the columns is a VARCHAR or CHAR data type and the length of the value being inserted is longer than the length of the column.
|
To illustrate, let’s say you have the following table:
CREATE TABLE [dbo].[Students] (
[StudentID] INT,
[FirstName] VARCHAR(10),
[LastName] VARCHAR(10)
)
Issuing the following INSERT statement will generate this error message:
INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )
Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Since the [FirstName] column will only accept 10 characters, the INSERT statement will fail because the length of the value being inserted is more than 10 characters.
The error can also be encountered when decreasing the length of a VARCHAR or CHAR column in a table that already contains data and the new length of the column is not long enough to accommodate the longest value in the column.
CREATE TABLE [dbo].[Students] (
[StudentID] INT,
[FirstName] VARCHAR(20),
[LastName] VARCHAR(20)
)
INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )
ALTER TABLE [dbo].[Students] ALTER COLUMN [FirstName] VARCHAR(10)
Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
Solution / Work Around:
To prevent this error from happening when inserting data to your table or when updating existing data in your table, always make sure that the string value you are trying to insert into your table can fit to the column you’ve specified. If you really need to insert the data to the table, your only option is to increase the length of the VARCHAR/CHAR column using the ALTER TABLE command:
ALTER TABLE [dbo].[Students] ALTER COLUMN [FirstName] VARCHAR(50)
If the error is caused by the decreasing of the length of the column, first check the maximum length that the column have and use that as the new length of the column. To determine the maximum length of a particular column, you can do the following:
SELECT MAX(LEN([FirstName])) FROM [dbo].[Students]
This will return the length of the longest [FirstName]. Then use this value as the new length of your column if you need to decrease its length.
|