Using OPENROWSET twice in one SQL statement

TimHudspith
TimHudspith used Ask the Experts™
on
I have two filestream columns in a table, one to hold a full-size photo, the other for a thumbnail image. I want to populate both columns at the same time. At the moment I'm uploading just the photo:

SET @sql = 
'INSERT INTO myTable (Filename, Image) ' +
'SELECT ''' + @Filename + ''' , Photo.* ' + 
'FROM OPENROWSET (BULK ''' + CONVERT(varchar(1000), @Path) + ''', SINGLE_BLOB) Photo'
EXEC sp_executesql @sql	

Open in new window



How do I amend this statement to include the thumbnail file?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
try
INSERT INTO myTable (imgid,imgname,fsImg,tnImg)

select
NEWID(),
'imgname',
bulkcolumn
FROM OPENROWSET(BULK 'C:\img\fsphoto.gif', SINGLE_BLOB) AS A,
bulkcolumn
FROM OPENROWSET(BULK 'C:\img\TNphoto.gif', SINGLE_BLOB) AS B,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial