Appending data to a sql text or ntext column
Today I came accross a problem where nvarchar data got larger than the maximum that can be stored in a record. I had to convert to a ntext column and append the data to it.
I quickly found out that I needed to use the UPDATETEXT function in combination with the TEXTPTR and DATALENGTH functions.
When you are working with the ntext type then the output of DATALENGTH must be divided by two to get the string length as it returns the length of the data in bytes.
DECLARE @TextToAppend nvarchar(max)
SET @TextToAppend = 'Append this piece of text'
DECLARE @pointer binary(16)
DECLARE @length int
SELECT
@pointer = TEXTPTR(MyLargeTextColumn)
,@length = DATALENGTH(MyLargeTextColumn)/2 -- Needed for ntext
FROM
MyTableWithLargeTextColumn
WHERE
ID = @ID
UPDATETEXT MyTableWithLargeTextColumn.MyLargeTextColumn @pointer @length 0 @TextToAppend
The above SQL code appends the text @TextToAppend to column MyTableWithLargeTextColumn.MyLargeTextColumn for the record with @ID.