Use write in Update

The update statement is enhanced in sql2005. The write function is provided for fields such as nvarchar (max), nvarchar (max), or varbinary (max.
Example:

Create table testBIGtext
(
TestBIGtextId int primary key,
Value varchar (max)
)
Go

Insert into testBIGtext
Values (1 ,'')

Next, we use write to update the value of the value field:

DECLARE @ offset int
SET @ offset = 0
WHILE @ offset <26
BEGIN
UPDATE testBIGtext
SET value. write (replicate (char (97 + @ offset), 1000), @ offset * 1000,100 0)
WHERE testBIGTextId = 1
SET @ offset = @ offset + 1
END

After the execution, the value will be 1000 a connected to 1000 B... (aaaa... bbb. ccc ...... zzz)