copy sql image field to another table

14 Nov 2005 5:49 AM

chris

Hi there, newbie adoneter here. I'm trying to update (or insert) a
record with a sql image field into table 1 based on an existing record
from a similar table (table 2) with vb.net

I used to open recordset use r.edit.....blah... r.update which clearly
can't happen anymore.
so what now ? open 2 sqldatareaders ,save image from table 2 to byte
var, then run 'update' statement on table 1 with sqlcommand ?
Am I on the right track ? tia !

16 Nov 2005 8:57 PM

David Sceppa [MSFT]

Chris,

     Both the UPDATE and INSERT queries allow you to reference another
table. Your best bet is to construct your UPDATE and INSERT queries so
they copy the image column from the source table to the destination table.
They may look something like:

INSERT INTO DestinationTable
SELECT ... FROM SourceTable
WHERE SourceTable.KeyColumn = ...

UPDATE DestinationTable
SET ImageColumn = SourceTable.ImageColumn
FROM SourceTable
WHERE DestinationTable.KeyColumn = SourceTable.KeyColumn
    AND DestinationTable.KeyColumn = ...

I'd recommend trying this approach using Query Analyzer or an ad hoc query
tool until you get the queries just the way you like them.

     If the application requires user interaction to determine which rows
to update/insert, you might want to use a SqlDataReader or DataTable to
retrieve the value(s) of the key column(s) and then use that information to
populate parameters on the INSERT or UPDATE query.

     I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2005 Microsoft Corporation. All rights reserved.