concatinating string greater than 8000 into a text column

Question

concatinating string greater than 8000 into a text column

Olja Dimitrijevic

Ten Centuries

Points: 1010
More actions
December 12, 2006 at 12:28 am

#92711

Hi,
I have some code that concatinates a string via a cursor into a varchar(8000) variable that then gets saved into the varchar(8000) field. I need to change the field size to be text, however I have not figured out how to concatinate strings that are greater than 8000 chars since there is no local variable of type text that can temporarily hold this data.
All help much appreciated!
Olja

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

situations like this is where I switch from doing everything in TSQL, to grabbing the data in .NET or vb6, where the String variable type does not have this limitation;

I'll do the work in a program, and then have the program insert/update the finished results into the TEXT field.

a cursor is the same as a recordset or datatable, and looping thru the whole set is really easy in a programming language.

in 2005, the VARCHAR(MAX) doesn't have the 8000 char handicap, so that's an option as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 2

Lowell, I did consider this! The issue is that a lot of my business logic resides at the db level and moving this particular functionality into asp.net will result in having to shift much of the code from tsql to c#.

MohammedU SSCoach Points: 19644 More actions · Answer 3

What about using table variable with text column....

MohammedU
Microsoft SQL Server MVP

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 4

Mohammed, how would i go about doing this. I have a cursor which I loop through and previously I would just append the text value to the varchar. How would I do this with the text column?

MohammedU SSCoach Points: 19644 More actions · Answer 5

I was thinking it can be used in conjuction with UPDATETEXT or WRITETEXT functions... but it all depends on requirement...

Can you post a sample code...

MohammedU
Microsoft SQL Server MVP

vadba SSChampion Points: 11133 More actions · Answer 6

Here's a simple example of how you could do that with UPDATETEXT. Remember that the output length of the text column when displayed in a SELECT statement in Query Analyzer is limit to a max of about 8000 characters. That's why I included datalength() at the end, so you can see the actual length.

CREATE TABLE #temp

(

id int

, data text

)

INSERT #temp (id, data)

SELECT 1, 'This is a row'

UNION ALL

SELECT 2, 'This is row 2.'

UNION ALL

SELECT 3, null

-- Example that appends 1000 lines to a text column

DECLARE @id int

, @count int

, @ptr varbinary(16)

, @updatestring varchar(8000)

, @eoln varchar(3)

SET @eoln = '.' + Char(13) + Char(10)

SET @id = 2 -- arbitrary selection for example purposes only

SELECT @ptr = TextPtr(data)

FROM #temp

WHERE id = @id

SET @count = 0 -- loop counter for this exmaple

WHILE @count < 1000

BEGIN

SET @count = @count + 1

SET @updatestring = 'This is appended line ' + CONVERT(varchar(10), @count) + @eoln

UPDATETEXT #temp.data @ptr null 0 @updatestring

END --WHILE

SELECT id, datalength(data), data FROM #temp

DROP TABLE #temp

Olja Dimitrijevic Ten Centuries Points: 1010 More actions · Answer 7

Thanks for the replies and samples! I managed to get my code updating the text fields as expected.