How to update text, ntext, or image fields in a table in SQL server 2000

At work, we often need to store information greater than 8 KB to the Text, ntext, and image fields of tables in the database. During data maintenance, in this case, you may save the storage information in one location to another.

When the data size is smaller than 8 K, we can use the following method
Note that the table structure used in the following example is as follows:
Photos table name
Field data type
Photo Image type
PersonIdentity varchar (18) primary key


Go
Declare @ ptrval binary (16) -- Define a pointer
Select @ ptrval = TEXTPTR (photo) from Photos where PersonIdentity = 'identifier of the person who needs to update information'
-- Set pointer
Declare @ InformationForInsert varchar (8000)
Select @ InformationForInsert = substring (photo,) from Photos Where PersonIdentity = 'identifier of the person who needs to extract information'
WRITETEXT Photos. photo @ ptrval @ InformationForInsert -- save information
Go

  
This method can only be used when the data size is less than 8 K. Note that when the data size is greater than 8 K, this method cannot be pulled.


 
 

Now let's analyze it. Theoretically, we need to provide information, including the location where the information is stored and the source of the information, which can be pulled.
 How can this be achieved?

 We can use thisUPDATETEXT.

Example:

Go

Declare @ ptr_NeedInsert binary (16) -- Define a pointer
Declare @ Ptr_InsertInformation binary (16) -- Define a pointer

Select @ ptr_NeedInsert = TEXTPTR (photo) from Photos where PersonIdentity = 'identifier of the person whose information needs to be updated'
-- Set pointer

Select @ Ptr_InsertInformation = TEXTPTR (photo) from Photos Where PersonIdentity = 'identifier of the person who needs to extract information'
-- Set pointer
 
UPDATETEXT Photos. Photo @ ptr_NeedInsert 0 null Photos. Photo @ Ptr_InsertInformation
Go

The UpDateText syntax is as follows:

UPDATETEXT { table_name.dest_column_name dest_text_ptr }
{ NULL | insert_offset }
{ NULL | delete_length }
[ WITH LOG ]
[ inserted_data
| { table_name.src_column_name src_text_ptr } ]

Parameters
Table_name.Dest_column_name
Table Name and field name corresponding to the pointer

Dest_text_ptr
Pointer to be maintained. Note that the pointer size must be of the binary (16) type.

Insert_offset
Insert position: 0 indicates the start position.

Delete_length
The length of the deleted data. Note that if it is NUll, the original data is cleared.

Inserted_data
Yes to insert to existingText,NtextOrImageColumnInsert_offsetLocation Data. Note that this is optional and there is a limit on the length of the inserted data.
Table_name.src_column_name
Used to insert a table orText,NtextOrImageColumn name.
Src_text_ptr
The pointer to the inserted data source.

Do not repost original articles-Xiao Xu 20070205 pm