Text column not storing more than 8000 characters

Question

I researched this and found that a text column in SQL Server can store a lot more than 8000 characters. But when I run the following insert in the text column, it only inserts 8000 characters:

UPDATE  a
SET [File] = b.Header + CHAR(13) + CHAR(10) + d.Detail + c.Trailer + CHAR(13) + CHAR(10) + CHAR(26)
FROM Summary a
JOIN #Header b ON b.SummaryId = a.SummaryId
JOIN #Trailer c ON c.SummaryId = a.SummaryId
JOIN #Detail d ON d.SummaryId = a.SummaryId
WHERE   
    a.SummaryId = @SummaryId

I am trying to generate a fixed width flat file and every row should be 3900 characters long, and they are in the respective temp tables. But when I do the insert in the permanent table, the Trailer data gets truncated.

I am adding char(10) + char(13) to add carriage return and line feed and char(26) for end of file, and it seems like they are adding characters to the fixed width layout.

Answer 1

The problem with your code is not the data type of the field that you store the value in, it's the type of the value that you put together to store in it.

The type of b.Header is not text but varchar, which is used as type for the whole expression. When the strings are concatenated, the result will be truncated to fit in a varchar value.

If you cast the first string to text, the whole expression gets that type, and can become longer than 8000 characters:

SET [File] = cast(b.Header as text) + CHAR(13) + CHAR(10) + d.Detail + c.Trailer + CHAR(13) + CHAR(10) + CHAR(26)

Naturally you should transition into using the new type varchar(max) instead of text, but that is not the reason for your problem.