problem updating text datatype

Hi All:
I am having trouble updating a TEXT column (I know that TEXT is a blob but I
am dealing with very large amounts of data that VARCHAR can not hold!) What
I want to happen is all rows from TableA that meet a certain criteria to be
inserted into TableB but if it already exists in TableB then update it
instead - this sounds simple enough and is working fine except that one of
the fields is TEXT datatype. So when I try to update I get an error "The
text, ntext, and image datatypes are invalid in this subquery or aggregate
expression." (Apparently you can not do an update select with a text
datatype) I did a search and found that I need to be using the UPDATETEXT
function - however I am not quite sure how this works. Any suggestions
would be greatly appreciated!
TableA
ID INT
MenuID INT
Content TEXT
TableB
ID INT
MenuID INT
Content TEXT
Thanks!Nancy,
Without seeing the query, it's impossible to know what the
problem is. Post the query that is causing the error, at
least, if you want a more specific answer.
In any case, it is certainly possible to update [text]
with an UPDATE statement. In general, you may want
something like this:
update TableB set
Content = TableA.Content
from TableA join TableB
on TableA.ID = TableB.ID
and TableA.MenuID = TableB.MenuID
insert into TableB(ID, MenuID, Content)
select ID, MenuID, Content
from TableA
where not exists (
select * from TableB as B2
where B2.ID = TableA.ID
and B2.MenuID = TableA.MenuID
)
Steve Kass
Drew University
Nancy Shelley wrote:

> Hi All:
> I am having trouble updating a TEXT column (I know that TEXT is a blob but
I
> am dealing with very large amounts of data that VARCHAR can not hold!) Wha
t
> I want to happen is all rows from TableA that meet a certain criteria to
be
> inserted into TableB but if it already exists in TableB then update it
> instead - this sounds simple enough and is working fine except that one of
> the fields is TEXT datatype. So when I try to update I get an error "The
> text, ntext, and image datatypes are invalid in this subquery or aggregate
> expression." (Apparently you can not do an update select with a text
> datatype) I did a search and found that I need to be using the UPDATETEXT
> function - however I am not quite sure how this works. Any suggestions
> would be greatly appreciated!
>
> TableA
> ID INT
> MenuID INT
> Content TEXT
> TableB
> ID INT
> MenuID INT
> Content TEXT
> Thanks!
>|||Hi Steve:
Thanks for the quick response. Supplying BLOB columns with text or image
data that's less than or equal to 8000 bytes in size is as straightforward
as updating any other type of column (you can use insert/update) but when
values are larger than 8000 you need to use updatetext or writetext. This
is what I was having trouble with - I know the syntax for updating my table
the regular way I just wasn't sure about using updatetext or writetext.
I ended up solving my own problem - my pointer was invalid. I was getting
an invalid pointer reference to the text column.
Thanks for your help!
Nancy
"Steve Kass" <skass@.drew.edu> wrote in message
news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
> Nancy,
> Without seeing the query, it's impossible to know what the
> problem is. Post the query that is causing the error, at
> least, if you want a more specific answer.
> In any case, it is certainly possible to update [text]
> with an UPDATE statement. In general, you may want
> something like this:
>
> update TableB set
> Content = TableA.Content
> from TableA join TableB
> on TableA.ID = TableB.ID
> and TableA.MenuID = TableB.MenuID
> insert into TableB(ID, MenuID, Content)
> select ID, MenuID, Content
> from TableA
> where not exists (
> select * from TableB as B2
> where B2.ID = TableA.ID
> and B2.MenuID = TableA.MenuID
> )
> Steve Kass
> Drew University
> Nancy Shelley wrote:
>|||Nancy,
I believe the syntax I suggested works for any length [text] or
[image] column.
You can also update or insert data into a text or image column if you
provide
it as a literal string or binary value. The 8000 byte limit does not
apply to
literals.
SK
Nancy Shelley wrote:

>Hi Steve:
>Thanks for the quick response. Supplying BLOB columns with text or image
>data that's less than or equal to 8000 bytes in size is as straightforward
>as updating any other type of column (you can use insert/update) but when
>values are larger than 8000 you need to use updatetext or writetext. This
>is what I was having trouble with - I know the syntax for updating my tabl
e
>the regular way I just wasn't sure about using updatetext or writetext.
>I ended up solving my own problem - my pointer was invalid. I was getting
>an invalid pointer reference to the text column.
>Thanks for your help!
>Nancy
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:OqsmT2jWFHA.3488@.tk2msftngp13.phx.gbl...
>
>
>