How to use UPDATETEXT within the context of inserting a new row

All -

I'm trying to insert a row into a table. One of the columns in this table is a text field. I can't figure out how to use UPDATETEXT to insert data into this text field for a brand new row. The examples I see all require a text pointer to the column. However - I haven't even inserted the row - so how would I create a text pointer?

Here's the example from Books Online -

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

How would I create the text pointer (TEXTPTR) for a record that hasn't even been inserted yet?

Incidentally - my issues would be solved if the data for this text column was always less than 8K (then I could use varchar(8000) - but that isn't always the case.

Help!

I'm assuming that you're running SS2000.

UPDATETEXT operates on tables.

If the data you want to manipulate isn't in a table, where is it?

|||

Note that UPDATETEXT is going away in a future version of SQL Server. Use varchar(max) if this is a 2005 database and then use the .WRITE construct in the UPDATE statement.

Well, you can just use a text parameter to a stored procedure and just pass in all of the text... That is the simplest answer. You will have to create the row first, and create the text pointer:

drop table test
go
create table test
(
testId int,
value text
)
go
insert into test(testId)
values (1)
go

Update test
set value = 'foo'
where testId = 1
and value is null

DECLARE @.ptrval binary(16)
SELECT @.ptrval = TEXTPTR(value)
FROM test
WHERE testId = 1

UPDATETEXT test.value @.ptrval null null 'b'
GO

select *
from test

Returns:

testId value

--

1 foob

|||Thanks so much - this seemed to work. And yes I am stuck with SQL Server 2000 right now. It seems to me so clunky to update text fields this way - so I'll be real happy once I migrate to 2005.

Thanks again for the help.