Concatenation of Text

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?Yes, UpdateText processes only one row per call.

The MDAC infrastructure was never meant to handle BLOBs, so trying to update a TEXT column in a million rows at once is really a bad idea. I'd either change the column datatype, or find a different way to acheive the same goal.

This just sounds like a problem sniffing eagerly at a new victim to me!

-PatP|||Thanks Pat. Unfortunately, I have to use a TEXT column because the data can exceed the 8k limit for VARCHAR and CHAR. I guess my choices are to use DTS and some sort of ActiveX script, write a script to concatenate before it hits the database, or do the unthinkable and write a cursor (although I don't really want to do that).

Dandy|||Nothing quite like being caught between the devil and the deep blue sea, is there? Given those choices, I'd opt for DTS.

Actually, cursors aren't logically bad, it is just that their performance is awful compared to set operations. Many databases like Oracle and Z-Series DB2 rely on cursors to do much of anything.

Be forewarned though, TEXT manipulation is slower than other datatypes. Just retrieving or storing a row with a TEXT or IMAGE column takes a lot longer than it does without those columns.

-PatP|||Of course it does, doh...First it needs to get the pointer (binary(16)), then retrieve 8K worth of data stored in a separate set of pages which results in at least 1 additional IO per row retrieved. BLOBs are wonderful (hehehe) when used for what they were invented, - not for set-based operations. And this is one of the few cases when a cursor may very well be applicable.

The MDAC infrastructure was never meant to handle BLOBs...Really? Who told you that?|||Really? Who told you that?It was either the guy who invented DTS, or the GPM for MS-SQL 7.0. They were both there, I just don't remember who actually said it and who just nodded sagely.

-PatP|||Oh, I see, and I'm an airplane :D|||I'm a teapot! I'm a teapot!

Concatenating columns with datatype ntext

How can I concatenate 2 columns, both of type ntext, in a select statement?
I get the following error.
"Invalid operator for datatype. Operator equals add, type equals ntext"You cannot use concatenation on TEXT datatype. If the length of the value is
less than 8000, you can use the CAST function to change it to VARCHAR
datatype and do the concatenation. Otherwise, you'll have to return them as
distinct values to the client and leverage the client's string concatenation
capabilities.
Anith|||Thanks, Anith, for your reply.
Actually I was trying some data transfer between 2 databases, so the client
option is not feasible for me and length of some column values is more than
a
varchar would permit, so that's not an option either. But knowing it is not
possible is good enough, so I can think of something else.
"Anith Sen" wrote:

> You cannot use concatenation on TEXT datatype. If the length of the value
is
> less than 8000, you can use the CAST function to change it to VARCHAR
> datatype and do the concatenation. Otherwise, you'll have to return them a
s
> distinct values to the client and leverage the client's string concatenati
on
> capabilities.
> --
> Anith
>
>|||You might also want to look at the UPDATETEXT function, but you'll have to
massage each row at a time. The general workaround is to get multiple
VARCHAR columns back like:
SELECT SUBSTRING( textcol, ( 0 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 1 * 8000 ) + 1, 8000 ),
SUBSTRING( textcol, ( 2 * 8000 ) + 1, 8000 ),
...
Also in SQL 2005 you will have character types with much better
capabilities.
Anith