I have a question on the UPDATETEXT function (SQL 2000)
The below query works and only updates the record where p.pub_id =
pr.pub_id.
I just don't quite understand why only 1 record is updated when the
UPDATETEXT statement
does not specify anything except pointer value. Is it no possible for 2
rows in a table with a text column to have the same pointer value?
Does this query scan all pr_info in the pub_info table?

USE pubs
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'
GO
DECLARE @ptrval binary(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
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO


Also, what is the importance of 'select into/bulkcopy', 'true' ?

Thanks

Artie (artie2269 (AT) yahoo (DOT) com) writes:
No, that is not possible. You get a text pointer for a specific row,
and then you work with that row.

In SQL 2005 there are new data types for blobs, varchar(MAX), nvarchar(MAX)
and varbinary(MAX). You work with these just like you work with regular
varchar, and no need for the klunky UPDATETEXT. That makes life a lot lot
easier.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Thanks for the info. I am aware of how much easier this is in SQL 2005 but
am stuck with 2000 in this case.
One more if I may...In the example syntax from BOL:

UPDATETEXT { table_name.dest_column_name dest_text_ptr }
{ NULL | insert_offset }
{ NULL | delete_length }
[ WITH LOG ]
[ inserted_data
I would like to take an extire TEXT column from a source table and append it
to a TEXT column in a destination table.
Do I need to get a start/end textptr from the source?



"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote

Artie (artie2269 (AT) yahoo (DOT) com) writes:
Yes, you would need a text pointer both for the source and target columns.
And you can only copy from one row to another at a time.

--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx