add many images as varbinary(max) to table

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 2005 Forums

Transact-SQL (2005)

add many images as varbinary(max) to table

Author

Topic

LD50
Starting Member

3 Posts

Posted - 2008-06-06 : 10:44:43

Hello,

I am trying to create a statement that will update many rows in a table with images, stored as varbinary(max), into a new column.

The path/file information is all stored in another table, but I can't find a way to update more than 1 at a time. Here is the statement that works for 1 row at a time:

update tblphotos set photo = (select
BulkColumn from
Openrowset( Bulk '\\**servername**\Props\Images\Don Giovanni 2002\Props\Horses Guts 2.jpg', Single_Blob) as photo
)
where photoseq = 27

but if I try to do something like this:

CREATE TABLE #temp (

photoSeq int,

photoLoc varchar(255),

photo varbinary(max)

)

INSERT #temp

select p.photoseq, f.fldlocation + p.photophyloc as files,
(select
BulkColumn from
Openrowset( Bulk f.fldlocation + p.photophyloc, Single_Blob) as photo
)
FROM txprops t
join tblfolders f on t.fldlocation = f.fldseq
join tblphotos p on p.photopropseq = t.propseq

begin tran

update a set photo = b.photo
from tblphotos a
join #temp b on a.photoseq = b.photoseq
where a.photoseq = b.photoseq

select * from tblphotos order by photophyloc

commit tran
drop table #temp

I get an error: Incorrect syntax near 'f'.
I think this is because I can only put a path in beside BULK.

I have investigated BULK and bcp commands, but cannot find anything to satisfy this. I tried the DTS package route, but am not getting very far.

Any suggestions? Is DTS the solution for me?

LD50
Starting Member

3 Posts

Posted - 2008-06-10 : 04:22:03

bump !

LD50
Starting Member

3 Posts

Posted - 2008-06-11 : 05:53:38

I ended up making an SSIS package to solve this.

I output all the file directorys and names to a .txt flat file. Created a Flat File in SSIS, attached it to Import Column, and attached that to OLE Database Destination.

It was very error bound and frustrating, but got the job done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources