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...
>

Move or delete file after uploading via SQL2k DTS Package

I have scheduled a DTS package (once a day) to open and import data from a fixed field file which is sent to our server via FTP from a data service bureau. The problem is that yesterday's file ends up remaining in the same location, while the new file being delivered by the service bureau via FTP does not over-write yesterday's file -- the file is added with a new reference...

Example:
Yesterday's file name "CLM_CLI.789" -- Today's file name "CLM_CLI.6890". Is there any way for SQL2k to delete the file after it is uploaded, or can anyone offer advice about how to use some other method to move the older file to another folder each day??

Thanks!
GlenHow does the DTS package know the name of today's file name?

I think you should be able to use xp_cmdshell to move the file to another directory.

Terri

move database record

hi,

i have no problem to select all records from the table, but how to copy or move these selected records to other table which contain same field as the orriginal.

thanksINSERT INTO table2 (col1, col2, col3...)
SELECT col1,col2, col3
FROM table1
WHERE ...|||ndinakar, can u guide me more details. example i have one table call student, inside got id and name and move this record to a table call student2. can u guide me complete sql statement, because i new to the sql, i bit confuse in line 1 and line 2. thanks

INSERT INTO table2 (col1, col2, col3...)
SELECT col1,col2, col3
FROM table1
WHERE ...|||

INSERT INTO student2 (id,name)
SELECT id,name
FROM student
WHERE {something}

{something} could be ID=some number or name='some name' etc

|||is't this method is for one reord only? how about if i want to move 1000 records from table student to table student2 ?. thanks|||

You move as many records as your SELECT statement returns.

Move data from one table to another

Trying to copy data from table one (name) to table two (n_system).
Field name.co_id to n_system.ultimate_id where name.id = n_system.id.
insert n_system (id, ultimate_id)
SELECT id, co_id FROM name
where name.id = n_system.id
Error message
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'n_system' does not match with a table name or alias
name used in the query.Use this format
insert into tabletwo select field1, field2 from tableone
Ben Nevarez, MCDBA, OCP
Database Administrator
"jp" wrote:

> Trying to copy data from table one (name) to table two (n_system).
> Field name.co_id to n_system.ultimate_id where name.id = n_system.id.
> insert n_system (id, ultimate_id)
> SELECT id, co_id FROM name
> where name.id = n_system.id
> Error message
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'n_system' does not match with a table name or alias
> name used in the query.
>|||Also, the problem is the where clause. You can not use n_system there in
that context. You need specify something, for example a constant
where name.id = 123
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Use this format
> insert into tabletwo select field1, field2 from tableone
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "jp" wrote:
>

Move data from one table to another

Trying to copy data from table one (name) to table two (n_system).
Field name.co_id to n_system.ultimate_id where name.id = n_system.id.
insert n_system (id, ultimate_id)
SELECT id, co_id FROM name
where name.id = n_system.id
Error message
Server: Msg 107, Level 16, State 3, Line 1
The column prefix 'n_system' does not match with a table name or alias
name used in the query.Use this format
insert into tabletwo select field1, field2 from tableone
Ben Nevarez, MCDBA, OCP
Database Administrator
"jp" wrote:
> Trying to copy data from table one (name) to table two (n_system).
> Field name.co_id to n_system.ultimate_id where name.id = n_system.id.
> insert n_system (id, ultimate_id)
> SELECT id, co_id FROM name
> where name.id = n_system.id
> Error message
> Server: Msg 107, Level 16, State 3, Line 1
> The column prefix 'n_system' does not match with a table name or alias
> name used in the query.
>|||Also, the problem is the where clause. You can not use n_system there in
that context. You need specify something, for example a constant
where name.id = 123
Ben Nevarez, MCDBA, OCP
Database Administrator
"Ben Nevarez" wrote:
> Use this format
> insert into tabletwo select field1, field2 from tableone
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "jp" wrote:
> > Trying to copy data from table one (name) to table two (n_system).
> > Field name.co_id to n_system.ultimate_id where name.id = n_system.id.
> >
> > insert n_system (id, ultimate_id)
> > SELECT id, co_id FROM name
> > where name.id = n_system.id
> >
> > Error message
> > Server: Msg 107, Level 16, State 3, Line 1
> > The column prefix 'n_system' does not match with a table name or alias
> > name used in the query.
> >
> >