reading text data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

reading text data

Author

Topic

ciaran
Starting Member

40 Posts

Posted - 2005-10-26 : 12:44:39

Hi,
I need to read text data from a row in a table (ke
y is campid) and update a different row in the same table with the data that i have read. i know that i have to use TEXTPTR , READTEXT and WRITETEXT. can some body give me an example of how this is done.

leifthoreson
Starting Member

16 Posts

Posted - 2005-10-26 : 15:52:40

Why use readtext/write text?
perhaps I don't understand what you are dealing with.
here is an example. ( from what I understand of your question)

-- table
CREATE TABLE [dbo].[texttable] (
[Camp_id] [char] (4) NOT NULL ,
[textfeild1] [text] NULL ,
[textfeild2] [text] NULL
)
-- insert text
insert into [texttable](Camp_id,Textfeild1) values (1,'this is a test of the text feildfor camp 1 ')
go
insert into [texttable](Camp_id,Textfeild1) values (2,'this is a test of the text feild for camp 2')
go
insert into [texttable](Camp_id,Textfeild1) values (3,'this is a test of the text feild for camp 3')
go

-- copy text to 2nd feild

update texttable
set textfeild2 = textfeild1

go
select * from texttable

dupati1
Posting Yak Master

123 Posts

Posted - 2005-10-26 : 16:00:57

CREATE Procedure myupdate
@RecordID integer
AS
declare @ptr binary(16)
,@newdata varchar(8000)

Select @newdata= mytext FROM mytable
Where RecordID=@RecordID

Select @ptr=textptr(mytext)
FROM mytable
Where RecordID=@RecordID

WRITETEXT mytable.mytext @ptr @newdata

GO

ciaran
Starting Member

40 Posts

Posted - 2005-10-27 : 05:30:37

quote:
Originally posted by dupati1

CREATE Procedure myupdate
@RecordID integer
AS
declare @ptr binary(16)
,@newdata varchar(8000)

Select @newdata= mytext FROM mytable
Where RecordID=@RecordID

Select @ptr=textptr(mytext)
FROM mytable
Where RecordID=@RecordID

WRITETEXT mytable.mytext @ptr @newdata

GO

Too dangerous. If i cast to varchar(8000) there is a danger that i will truncate the data. There is a very strong possability that the data is longer than 8000.

ciaran
Starting Member

40 Posts

Posted - 2005-10-27 : 05:33:48

quote:
Originally posted by leifthoreson

Why use readtext/write text?
perhaps I don't understand what you are dealing with.
here is an example. ( from what I understand of your question)

-- table
CREATE TABLE [dbo].[texttable] (
[Camp_id] [char] (4) NOT NULL ,
[textfeild1] [text] NULL ,
[textfeild2] [text] NULL
)
-- insert text
insert into [texttable](Camp_id,Textfeild1) values (1,'this is a test of the text feildfor camp 1 ')
go
insert into [texttable](Camp_id,Textfeild1) values (2,'this is a test of the text feild for camp 2')
go
insert into [texttable](Camp_id,Textfeild1) values (3,'this is a test of the text feild for camp 3')
go

-- copy text to 2nd feild

update texttable
set textfeild2 = textfeild1

go
select * from texttable

I think you gave me an idea. I can use a self join and this will allow me update the data. The issue is that i have the data that i need in one row of the table and i need to pull it out and put it in a different row in the same table. Its all text data so one cannot use a sub query Nor pull it into a varchar variable for fear of truncation

socnob
Starting Member

10 Posts

Posted - 2007-03-13 : 00:38:28

Could someone provide the syntax for this? I too am trying to copy a text field from one record to another and cannot use a temporary varchar(8000) for fear of having the data truncated.

socnob
Starting Member

10 Posts

Posted - 2007-03-15 : 19:39:04

Nevermind. I got it. Its:
===============
declare @ptrSrc binary(16)
declare @ptrDest binary(16)

SELECT @ptrVar = TEXTPTR(colName)
FROM tblName
WHERE <whereClauseForSourceRecord>

SELECT @ptrDest = TEXTPTR(colName)
FROM tblName
WHERE <whereClauseForDestRecord>

UPDATETEXT tableName.colName @ptrDest 0 NULL tableName.colName @ptrSrc
===============

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources