WRITETEXT Statement

Permits nonlogged, interactive updating of an existing text or image field. This statement completely overwrites any existing data in the column it affects. By default, WRITETEXT statements are not logged so that the transaction log won't fill up with the large amounts of data that often make up these datatypes.

Syntax

WRITETEXT [[database.]owner.]table_name.column_name text_ptr
[WITH LOG] data

where

table_name.column_name

Specifies the text column and table to be updated. The database name and owner name are optional.

text_ptr

Is a value that stores the pointer to the text or image data.

WITH LOG

Logs the inserted text or image data, which aids media recovery. Because text data quickly increases the size of the transaction log, be sure that the transaction log resides on a separate database device.

Remarks

The WRITETEXT statement completely overwrites any existing data in the column it affects. Use WRITETEXT to replace text data and UPDATETEXT to modify text data. UPDATETEXT is more flexible because it changes only a portion of a text of image value rather than the entire value.

By default, WRITETEXT is a nonlogged operation. This means that text or image data is not logged when it is written into the database. To use WRITETEXT in its default, nonlogged state, the system administrator must use the sp_dboption system stored procedure to set select into/bulkcopy, which allows nonlogged data to be inserted.

For WRITETEXT to work, the column must already contain a valid text pointer. There are two ways to create a text pointer:

• Insert actual data into the text or image column
• Update the column with data or a NULL

Because an initialized text column uses 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.

The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120K for text and image data.

You cannot use WRITETEXT on text and image columns in views.

The DB-Library functions dbwritetext and dbmoretext and the ODBC function SQLPutData are faster and use less dynamic memory than WRITETEXT. These functions can insert up to 2 gigabytes of text data.

Caution After using the WRITETEXT statement, you must execute DUMP DATABASE. After nonlogged operations occur within a database, the DUMP TRANSACTION statement cannot be performed. For details, see the DUMP statement.

Text and Image
Examples

A.    READTEXT Statement

This example selects the second through twenty-sixth characters of the pr_info column in the pub_info table.

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
    FROM pub_info pr, publishers p
        WHERE p.pub_id = pr.pub_id 
        AND p.pub_name = 'New Moon Books'
READTEXT pub_info.pr_info @ptrval 1 25

B.    Use WRITETEXT to Change Text Data

This example puts the text pointer into the local variable @ptrval, and then WRITETEXT places the new text string into the row pointed to by @ptrval.

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
    FROM pub_info pr, publishers p
        WHERE p.pub_id = pr.pub_id 
        AND p.pub_name = 'New Moon Books'
WRITETEXT pub_info.pr_info @ptrval 'New Moon Books (NMB) has just released another top ten publication. With the latest puglication this makes NMB the hottest new publisher of the year!'

Note that publication is spelled incorrectly (puglication) in this example. In example C, this value is corrected with UPDATETEXT.

C.    Use UPDATETEXT to Modify Text Data

This example puts the text pointer into the local variable @ptrval, and then uses UPDATETEXT to update the spelling error in example B.

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(pr_info) 
    FROM pub_info pr, publishers p
        WHERE p.pub_id = pr.pub_id 
        AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b' 
go

See Also