I need to concat ntext

[twifosp]

I have a database that will build dynamic web pages using HTML stored in ntext fields.

You can have a parent id that corresponds to a full page, and then child id's that actually contain the html and the body of the page.

Example:

parent_guid	child_guid 	body
1		1a		HTML1a
1		1b		HTML1b
1		1c		HTML1c
2		2a		HTML2a
2		2b		HTML2b

The body column is an ntext field.

For various reasons I need to move a large portion of the data to another type of database which is less dynamic. All of the info will be contained on one row now:

The new table would be like:

parent_guid	body
1		HTML1a HTML1b HTML1c
2		HTML2a HTML2b

The immediate solution that would pop into my mind would be to write a UDF that coalesce's the body fields. However,
1. The body data type is ntext
2. A large portion of the body fields are greater than 8000 characters so converting to varchar will not work.

So, I'm at a loss of how to proceed. Anyone have any ideas?

 

[amrita418]

I think you can still use a function to concatenate values for each parent_guid field.

Check the code here (thread183-1103577) and see if that works for you.

Regards,
AA

 

[twifosp]

amrita -- unfortunetly the function requires @strStates to be a varchar, in this instance, the column that will be passed to the varchar can be greater than 8000 characters. So the function will fail.

 

[twifosp]

Any ideas?

 

[twifosp]

Bump.

 

[NoCoolHandle]

Text datatypes can be icky...

How about building a temptable and then doing an insert followed by some updatetext statements..


However, I tend to think this is MUCH easier to do at the client.