CODEWITHSUNDEEP
sql-server
ner0
ner0

Reputation: 84

String or binary data would be truncated. — even though the column length was increased

I am having an issue with SQL Server where I need to make a column bigger, and I thought I had, but then I can't fit in it data bigger than the initial size of the column.

The column [columnName] initially was VARCHAR(50) and I used the command below to change the size to VARCHAR(100):

ALTER TABLE dbo.tableName 
    ALTER COLUMN columnName VARCHAR(100) NOT NULL;

In the table design view I can see that the size is now 100.

Despite this, I still get the error

String or binary data would be truncated.

when trying to fit data that exceeds the initial space of 50 bytes.

What am I missing?

Thank you.

Upvotes: 1

Views: 2152

Answers (1)

Anand
Anand

Reputation: 1123

Let's say your insert statement is something like:

insert into dbo.tableName (
    ...,
    columnName,
    ...
    )
select ...,
    columnName,
    ...
from dbo.anotherTableName

Then, use this SQL to find out the largest value you are trying to insert into that column:

select max(len(columnName)) as minVarcharLengthRequired
from dbo.anotherTableName

And then use something larger than that for the alter table statement.

Upvotes: 1

Related Questions