Permits minimally logged, interactive updating of an existing text or image column.


writetext [[database.]owner.]table_name.column_name
	text_pointer [readpast] [with log] data



is the name of the table and text or image column to update. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.


a varbinary(16) value that stores the pointer to the text or image data. Use the textptr function to determine this value, as shown in example 1. text and image data is not stored in the same set of linked pages as other table columns. It is stored in a separate set of linked pages. A pointer to the actual location is stored with the data; textptr returns this pointer.


specifies that the command should modify only unlocked rows. If the writetext command finds locked rows, it skips them, rather than waiting for the locks to be released.

with log

logs the inserted text or image data. The use of this option aids media recovery, but logging large blocks of data quickly increases the size of the transaction log, so make sure that the transaction log resides on a separate database device. See create database, sp_logdevice, and the System Administration Guide for details.


is the data to write into the text or image column. text data must be enclosed in quotes. image data must be preceded by “0x”. Check the information about the client software you are using to determine the maximum length of text or image data that can be accommodated by the client.


Example 1

This example puts the text pointer into the local variable @val. Then, writetext places the text string “hello world” into the text field pointed to by @val:

declare @val varbinary(16) 
select @val = textptr(copy) from blurbs 
    where au_id = "409-56-7008" 
writetext blurbs.copy @val with log "hello world"

Example 2

declare @val varbinary(16) 
select @val = textptr(copy) 
from blurbs readpast
    where au_id = "409-56-7008" 
writetext blurbs.copy @val readpast with log "hello world"


Using the readpast option


SQL92 – Compliance level: Transact-SQL extension.


writetext permission defaults to the table owner, who can transfer it to other users.

See also


Datatypestext and image datatypes