To update text or image values, use the WRITETEXT statement. By default, WRITETEXT statements (using text or image data) are not logged, so that the transaction log will not fill up with large amounts of data.
Note The system administrator must use the sp_dboption system procedure to set select into/bulk copy to true for the WRITETEXT statement to be used in its default, nonlogged state.
The WRITETEXT statement completely overwrites any existing data in the column it affects. For WRITETEXT to work, the column must already contain a valid text pointer. There are two ways to create a text pointer:
Important After a WRITETEXT operation that does not include the WITH LOG option, a DUMP TRANSACTION operation will not archive changes made to a text or image column, because these changes were not recorded in the transaction log. In this situation, the only way to archive such changes is to use the DUMP DATABASE statement.
Because an initialized text column uses at least one page (2K) of storage, even to store a couple of words, SQL Server saves space by not initializing text columns when explicit or implicit null values are placed in text columns with INSERT. For example:
INSERT publishers
VALUES ('9925', 'New Publisher', 'Redmond', 'WA', 'USA')
go
INSERT pub_info
VALUES ('9925', NULL, NULL)
go
After an INSERT operation, such as the one in the preceding example, you can use this UPDATE statement to initialize the text column:
UPDATE pub_info SET pr_info = null WHERE pub_id = '9925'
Once you have initialized the pointer, you can use WRITETEXT. This example adds text to an existing row in the publishers table:
DECLARE @val varbinary(16) SELECT @val = textptr(pr_info) FROM pub_info WHERE pub_id = '9925' WRITETEXT pub_info.pr_info @val WITH LOG 'Information about New Publisher.'
This example puts the text pointer into the local variable @val. Then WRITETEXT places the new text string into the row pointed to by @val.
For details on WRITETEXT, see the WRITETEXT statement in the Microsoft SQL Server Transact-SQL Reference.