February 12, 2009 at 3:56 am
I am facing the following problem.
One of the columns of my table is of 'Text' data type.
I am using updatetext to update the content of 'text' column.
syntax of updatetext statement is like this.
updatetext [tablename].[columnName] [pointer] [insertOffset] [deleteOffset] [input data]
I need to write a select statement in place of [input data].
I have tried something like this but in vain.
---------------------------------------------------------------------------
UPDATETEXT #result.body @ptr NULL 0 (SELECT * from
tbllead)
--------------------------------------------------------------------------
any solution would be greatly obliged.
February 12, 2009 at 4:33 am
you can't put a select statement there, only a string. you cannot select all columns in a table as a string...you'd have to build a string and use that.
you'll need to do something like :
declare @string varchar(max)
SET @string = ''
SELECT @string = @string + ISNULL(oneColumn,'') + ',' from MyFruits
SELECT @string --returns something like 'APPLES,BANANAS,CHERRIES,GRAPES,KIWI,ORANGES,'
UPDATETEXT #result.body @ptr NULL 0 @string
If you really needed more than one column, say 3 columns as an example, we'd need more information
Lowell
February 12, 2009 at 5:09 am
The thing is that I need to store the output of a query (which is more than 8000 char) into the text column.
And other thing is that string operations like '+' do not work on Text data type.
Pls opine.
February 12, 2009 at 5:19 am
you can do it in 8000 char bites. the NULL parameter in your original statement below is WHERE to insert more text in the text field, and thenext parameter is how many characters to remove if needed..
UPDATETEXT #result.body @ptr NULL 0 @8000CharString
UPDATETEXT #result.body @ptr 8001 0 @8000CharString
UPDATETEXT #result.body @ptr 16001 0 @8000CharString
Lowell
February 12, 2009 at 5:43 am
Hey Powell,
Here is my query:
Select replace( subp.vproduct+
l.vtype+
l.nasm_id as varchar(10)) +
l.vasm_name+
l.vil_area +
l.vilbranch +
cast(count(*) as varchar(50)),char(39),'`')
from tbllead l
The output of this query is more than 8000 characters. I want to store the output of this query in text datatype column.
Please suggest.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply