|
|
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)
)
INSERT INTO [dbo].[Students] ( [StudentID], [FirstName], [LastName] )
VALUES ( 12345, 'Rumpelstiltskin', '' )
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)
Solution/Workaround:
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.
|