Insert multiple results into a Text column....

[ranta]

I am trying to insert a list of id's into a text column in a comma deliminated format. The list is coming from the ID's of another table.

Is there anyway I can do this without looping through the table and building the variable with ',' and then updating the result table?

I was hoping that I could run an update SQL statement and populate the column with the results of a sub-select statment(is it possible to make this result build a string instead of returning each result one at a time??)

Cheers,

Ranta

 

[ranta]

To confirm exactly what I am trying to achieve.. I have a temp table with over 6000 records in it and I am trying to build these into a comma delminated string and update into a TEXT column in another table.

I am running the following bit of code to build the string;

DELCARE @SQLDisti as varChar(8000)
SELECT @SQLDisti = isnull(@SQLDisti + ',', ' ') + cast(partnerID as varchar(10)) FROM #TempItems

The problem I have is that the maximum length of 8000 is exceeded... is there anyway I can run this select statement within the update statment and therefore loose the need for the @SQLDisti variable??

Thanks.

 

[CarlsonB100]

If you already have a text column to store the results, you can use the UPDATETEXT command (or you can create a temp table with a text column if you don't have one). You would still be doing your looping/cursor processing, but you could get around the 8000 character limit.

The following example appends data to a text column in a temp table:

ex.
-- I am assuming #temp has already been created

-- get pointer to text column
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR(textField) FROM #temp

DECLARE @textline varchar(8000)

-- perform following in loop or cursor
-- create some text value
SELECT @textline = 'another value'
-- add to text column
UPDATETEXT #temp.textField @ptrval null 0 @textline