text datatype - 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)

text datatype

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2003-05-06 : 17:17:58

I have one table with duplicate records. I would like to 'merge' the information from the second record into the first. I have multiple columns, one of them being a text datatype.

I am able to update all of the columns but the TEXT column. How do I update the first record, which may or may not have data in this column, with the text data from the second record? The text data from the second record must be inserted after the text data from the first record, but it must be all inclusive. I can't lose any of the information.

Will someone please point me in the right direction?

Thanks!
Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-06 : 17:39:39

Have you looked at UPDATETEXT and WRITETEXT yet (information in BOL)?

Tara

TJ
Posting Yak Master

201 Posts

Posted - 2003-05-06 : 17:46:30

quote:

Have you looked at UPDATETEXT and WRITETEXT yet (information in BOL)?

Yes, but am having trouble comprehending this:

Examples

This example puts the text pointer into the local variable @ptrval, and then uses UPDATETEXT to update a spelling error.

USE pubs

GO

EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'true'

GO

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(pr_info) 

FROM pub_info pr, publishers p
		WHERE p.pub_id = pr.pub_id 
		AND p.pub_name = 'New Moon Books'
UPDATETEXT pub_info.pr_info @ptrval 88 1 'b' 
GO
EXEC sp_dboption 'pubs', 'select into/bulkcopy', 'false'
GO

specifically the updatetext statement.

I understand this:

pub_info.pr_info (table.column)
@ptrval (pointer)

but not this:
88 1 'b'

Is it going to position 88, selecting the first character, and then replacing it with a 'b'?

Thanks for your help!
Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-06 : 18:33:49

Almost
at position 88 delete one character then insert 'b' at that postion

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TJ
Posting Yak Master

201 Posts

Posted - 2003-05-07 : 10:01:47

quote:

Almost
at position 88 delete one character then insert 'b' at that postion

Thank you!

I have this code:

DECLARE @ptrval binary(16)

SELECT @ptrval = TEXTPTR(notes)
  FROM tjb_rdx_test
  WHERE dupid = 84 

UPDATETEXT tjb_rdx_test.notes @ptrval null 0 (select notes 
                                                from tjb_rdx_test 
                                                where dupid = 85)

However, it does not 'copy' the data from the record where the dupid = 85 to the record where dupid = 84.

What am I missing?

SQL 7, notes(ntext), dupid (int)

Thanks!
Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

Edited by - tj on 05/07/2003 10:17:14

TJ
Posting Yak Master

201 Posts

Posted - 2003-05-07 : 10:49:36

This code works:

DECLARE @ptrval binary(16)
declare @text     nvarchar(4000)

SELECT @ptrval = TEXTPTR(notes)
  FROM tjb_rdx_test
  wHERE dupid = 84 

set @text = (select cast(notes as nvarchar(4000)) from tjb_rdx_test where dupid = 85)

UPDATETEXT tjb_rdx_test.notes @ptrval null 0 @text

Thanks for you help!!!

Teresa

"It's not what you take with you when you go;
but what you leave behind you when you're gone."

Edited by - tj on 05/07/2003 10:50:58

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources