SQL Concatenation filling up tempDB -

we attempting concatenate possibly thousands of rows of text in sql single query. query have looks this:

declare @concattext nvarchar(max)  set @concattext = ''  update top (select max(pagenumber) + 1 #orderedpages) [#orderedpages]  set @concattext = @concattext + [columntext] + ' ' (rtrim(ltrim([columntext])) != '') 

this working fine functional standpoint. issue we're having columntext can few kilobytes in length. result, we're filling tempdb when have thousands of these rows.

the best reason have come we're doing these updates @concattext, sql using implicit transactions strings immutable.

we trying figure out way of solving problem , far have 2 possible solutions: 1) concatenation in .net. ok option, that's lot of data may go across wire.

2) use .write operates in similar fashion .net's string.join method. can't figure out syntax bol doesn't cover level of sql shenanigans.

this leads me question: .write work? if so, what's syntax? if not, there other ways without sending data .net? can't use for xml because our text may contain illegal xml characters.

thanks in advance.

i'd @ using clr integration, suggested in @martin's comment. clr aggregate function might ticket.