how to update a text field from a file

[PontiacCZ]

Hello,

I am trying to load a text file (HTML actually) and replace already existing table field (of "text" data type) with this file's content.

SQL server: 8.0 (SQL 2000 standard)
table: KART_CUSTOMERS
column: Data
input file: C:\temp\input.html

With help of web resources I was able to construct these SQL commands:

DECLARE @ptrval binary(16);
SELECT @ptrval = TEXTPTR(Data) 
FROM KART_CUSTOMERS KC
WHERE KC.CUSTOMER_ID = 'T0000' AND ID = 0;

WRITETEXT KART_CUSTOMERS.Data @ptrval C:\temp\input.html;

After running this I get following error:

Msg 186, Level 15, State 1, Server HP14281105721, Line 7
Data stream missing from WRITETEXT statement.

Yes, I feel the C:\temp\input.html; part is not correct. But if I put the filename into quotes, it would insert the filename itself (have just tried it ).

The official manual (

http://msdn.microsoft.com/en-us/library/aa260673(v=sql.80).aspx)

only says:

data
Is the actual text, ntext or image data to store. data can be a literal or a variable. The maximum length of text that can be inserted interactively with WRITETEXT is approximately 120 KB for text, ntext, and image data.

So what is the correct syntax for redirecting the input from a file?

 

[djj55]

A quick answer. Try

UPDATETEXT [BULK] { 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 } ]

Sorry got to start work so cannot analyze the problem.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[PontiacCZ]

I decided to use WRITETEXT instead of UPDATETEXT because of

http://msdn.microsoft.com/en-us/library/aa260658(v=sql.80).aspx

(the very first paragraph):

... Use UPDATETEXT to change only a portion of a text, ntext, or image column in place. Use WRITETEXT to update and replace an entire text, ntext, or image field.

And since I want to replace the entire text field I need to use WRITETEXT.

But the main question still remains: the input data stream from file - what is the SQL syntax for it?