Hello,
I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.
Something like - Note: the codes below doesn't work!!
DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA
FETCH NEXT FROM _rows INTO @.AccID
WHILE (@.@.fetch_status <> -1)
BEGIN
UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @.AccID)
WHERE AccID = @.AccID
FETCH NEXT FROM _rows INTO @.AccID
END
Thanks in advance
I tested something like this:(Assume you are using SQL Server 2000 text fields)
You can try it out in your code.
DECLARE @.ptr varbinary(50),@.ptr2 varbinary(50)
DECLARE @.len int, @.len2 int
update Table_B SET colB=(SELECT colA FROM Table_A where Accid=1) WHERE Accid=1
select @.ptr = TEXTPTR(colB), @.len=datalength(colB), @.ptr2 = TEXTPTR(report), @.len2=datalength(report) from Table_B WHERE Accid=1
UPDATETEXT Table_B.report @.ptr2 @.len2 0 Table_B.colB @.ptr
In SQL Server 2005, you can define Varchar(MAX) field, it will be a lot more easy to manipulate by using .WRITE function.
|||
Thanks Limno,
Sorry I could reply this sooner. This is one slick trick - appreciated.
However, I couldn't get this working as the way you have. Not sure what I am missing.
I kept having the error
Server: Msg 7116, Level 16, State 4, Line 13
Offset 17 is not in the range of available text, ntext, or image data.
The statement has been terminated.
DECLARE @.ResultID as int
DECLARE @.AccID int
DECLARE @.Heading varchar(255)
DECLARE @.ptrReport varbinary(16)
DECLARE @.ptrTmpField varbinary(16)
DECLARE @.Len1 int, @.Len2 int
SELECT @.ptrTmpField = TEXTPTR(tmpField), @.Len1 = Datalength(tmpField),
@.ptrReport = TEXTPTR(Report), @.len2 = Datalength(report)
FROM PHILTBL2 WHERE AccID = 1624728
UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 tmpField @.ptrTmpField
Thanks
|||
If I changed the updatetext statement as
UPDATETEXT PHILTBL2.Report @.ptrReport @.len2 0 @.ptrTmpField
The error went away, but I get gebrish text in my report field.
Look like the textptr doesn't work
|||
Check this link that Jared Ko provided earlier today:
http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html
|||
Thanks CetinBasoz, but the the problem I am dealing with is the text field and it's a different animal than varchar field. :-(
Check here for more info
http://msdn2.microsoft.com/en-us/library/ms189466.aspx
|||
Please see below scripts for some examples on how to use UPDATETEXT with TEXTPTR.
-- Script #1
/* To concatenate several binary values into one image field: */
create table #Bin ( SegmentID int IDENTITY ( 1 , 1 ) , Segment varbinary(2) )
insert #Bin values (0x11)
insert #Bin values (0x22)
select * from #Bin
go
declare @.imageptr varbinary(16), @.segmentid int, @.segment varbinary(16)
create table #AllBin ( Segments image null )
-- Get valid pointer first
insert #AllBin values ( 0x0 )
select @.imageptr = TEXTPTR( Segments ) from #AllBin
-- Set data to null
update #AllBin set Segments = null
select @.segmentid = -1
while(1=1)
begin
select @.segmentid = min(segmentid)
from #Bin
where segmentid > @.segmentid
if @.segmentid is null break
select @.segment = segment from #Bin where segmentid = @.segmentid
updatetext #AllBin.Segments @.imageptr null 0 @.segment
end
select * from #AllBin
go
drop table #Bin
drop table #AllBin
go
-- Script #2
create procedure #t (
@.t1 text , @.i1 image, @.t2 text, @.i2 image, @.t3 varchar(30), @.i3 varbinary(2)
)
as
declare @.tptr varbinary(16), @.iptr varbinary(16), @.tpos int, @.ipos int
create table #blob(id int identity, t text, i image)
insert #blob values(@.t1, @.i1)
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
update #blob set t = @.t2, i = @.i2 where id = @.@.identity
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
select @.tptr = TEXTPTR(t), @.tpos = PATINDEX('%TEXT...%', t) - 1,
@.iptr = TEXTPTR(i), @.ipos = 2
from #blob
updatetext #blob.t @.tptr @.tpos 0 @.t3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
updatetext #blob.i @.iptr @.ipos 1 @.i3
select id, convert(varchar(50), t) as text_val, convert(varbinary, i) as image_val
from #blob
go
exec #t 'SOME TEXT HERE...', 0x02498765bcde3,
'MODIFIED TEXT...', 0xab86ec64,
'(INSERT BEFORE TEXT) ', 0xcd -- replace 3rd byte
-- Inserted text & image value
/* id text_val image_val
-- -- --
1 SOME TEXT HERE... 0x002498765BCDE3
*/
-- Updated text & image value. This one replaces the existing values
/*
id text_val image_val
-- -- --
1 MODIFIED TEXT... 0xAB86EC64
*/
-- Modified text value only. This one inserts some text into the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86EC64
*/
-- Modified image value only. This one changes a byte in the existing value
/*
id text_val image_val
-- -- --
1 MODIFIED (INSERT BEFORE TEXT) TEXT... 0xAB86CD64
*/
go
drop proc #t
go
Also, here is a link to a SP that show how to generate text value from multiple strings.
|||Sorry my bad. When I wrote it was around 3 AM here.
