Move Text Data

Does anyone have a proven way to directly move text data (text/image data
type) from a field in one table to a field in another table?
I need to update (not insert) text data in one table with data text data
that exists in another table.
I can't use local variables to process the transaction...
ThanksTake a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online.
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||I have looked at these functions... but can't make them work.
the following code gets this error: < Data stream missing from
WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext ppd_object_version.script_text @.ptr1 readtext
ppd_setup_log.script_text @.ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext ppd_object_version.script_text @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eKRzmBWFGHA.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books
Online.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
data
>|||Another possibility is to use VBScript or some other language. For example,
this is pretty trivial if it is a one-time thing:
set conn = CreateObject("ADODB.Connection")
conn.open "<connection string>"
set rs = conn.execute("SELECT primary_key,text_column FROM table")
do while not rs.eof
pk = rs(0) : tc = replace(rs(1), "'", "''")
sql = "UPDATE other_table SET text_column = '" & tc & _
"' WHERE primary_key = " & pk
conn.execute sql,,129
rs.movenext
loop
rs.close: set rs = nothing: conn.close: set conn = nothing
Now, depending on the size of your table, it may take a while, so you may
have to play with commandTimeout. But that took 30 seconds to throw
together, versus who knows how long it will take to develop something using
READTEXT/WRITETEXT etc.
A
"rmg66" <rgwathney__xXx__primepro.com> wrote in message
news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
> Does anyone have a proven way to directly move text data (text/image data
> type) from a field in one table to a field in another table?
> I need to update (not insert) text data in one table with data text data
> that exists in another table.
> I can't use local variables to process the transaction...
> Thanks
>|||here's a clearer version of the code:
I have looked at these functions... but can't make them work.
the following code gets this error:
< Data stream missing from WRITETEXT statement. >
-- create pointers
declare @.ptr1 binary(16)
declare @.ptr2 binary(16)
-- initialize pointers
select @.ptr1 = textptr(text_field1) from table1 where pk = 1
select @.ptr2 = textptr(text_field2) from table2 where pk = 1
-- move text
writetext table1.text_field1 @.ptr1 readtext table2.text_field2 ptr2 1 100
the writetext statement seems only to want a inline string, as follows...
writetext table1.text_field1 @.ptr1 'xxxxxxxxx'
Robert
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:eKRzmBWFGH
A.140@.TK2MSFTNGP12.phx.gbl...
> Take a look at READTEXT, WRITETEXT, TEXTPTR and UPDATETEXT in Books Online
.
>
> "rmg66" <rgwathney__xXx__primepro.com> wrote in message
> news:Oyen1sVFGHA.648@.TK2MSFTNGP14.phx.gbl...
>