UPDATETEXT question

[Artie]

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_inf o @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

[Erland Sommarskog]

Re: UPDATETEXT question

Artie (artie2269@yaho o.com) writes:

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?

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Artie]

Re: UPDATETEXT question

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
| { table_name.src_ column_name src_text_ptr } ]

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@sommars kog.sewrote in message
news:Xns9A4CF93 F5AYazorman@127 .0.0.1...

Artie (artie2269@yaho o.com) writes:

>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?

>
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@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

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

[Erland Sommarskog]

Re: UPDATETEXT question

Artie (artie2269@yaho o.com) writes:

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
| { table_name.src_ column_name src_text_ptr } ]
>
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?

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@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx