Filename Variable Trouble

I want have multiple records from 1-4500 that I must insert a picture into the database for. I am trying to set up a variable to do this if someone can show me my error I would apprieciate it. This table has 4 columns (id# Float is a FK) (ImageName nvarchar (10)) (ImageFile Varbinary(Max)) (rec# int PK Increment Seed).

declare @.jpg as int

Set @.jpg = 0

While @.jpg <4500

begin

set @.jpg = (@.jpg + 1)

use consumer

insert photo (id#, ImageName, ImageFile)

Select @.jpg, '@.jpg.jpg',

bulkcolumn from Openrowset (Bulk 'D:\Data\Pics\@.jpg.jpg', Single_Blob) as 'ImageFile'

My image name is the ID# in a jpg file format. So I want to insert for

example:

ID# ImageName ImageFile Rec#

1 1.jpg <binary value> 1

2 2.jpg <binary value> 2

3 3.jpg <binary value> 3

4 4.jpg <binary value> 4

there are also id#'s without pictures should this just skip them?

|||

You cannot use a variable like this:

'D:\Data\Pics\@.jpg.jpg' or this: '@.jpg.jpg',

You will need to do something like:

use consumer
go

declare @.jpg as int, @.query varchar(1000)
Set @.jpg = 0
While @.jpg < 4500
begin
set @.jpg = (@.jpg + 1)

set @.query = '
insert photo (id# ,ImageName ,ImageFile)
Select ' + cast(@.jpg as varchar(10)) + '
, ''' + cast(@.jpg as varchar(10)) + '.jpg' + '''
,bulkcolumn
from Openrowset (Bulk ''' + 'D:\Data\Pics\' + cast(@.jpg as varchar(10)) + '.jpg'', Single_Blob) as ImageFile'

select @.query
exec (@.query)
end

I don't have any experience with he bulk stuff, but the code runs, and appears to try to do the openrowset because it claims that the file doesn't exist. I got some of the idea from: http://community.sgdotnet.org/blogs/chuawenching/archive/2006/04/03/25601.aspx

|||

Thank you very much it does work I just have to build in if the file doesn't exist move on the the next record. This is great work Thank you again.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

Msg 4860, Level 16, State 1, Line 2

Cannot bulk load. The file "D:\Data\Pics\33.jpg" does not exist.

|||

I too am having difficulty how to construct a variable for a file name.

I am trying to emulate the variable re: your blob...

-- I want to use some kind of variable, like this to use in the file:

DECLARE @.FIL VARCHAR(65)

SET @.FIL = 'C:\company folders\Documentation\INVENTORY.xls;'

--

SELECT FROM OPENROWSET('MSDASQL', 'Driver=Microsoft Excel Driver (*.xls);DBQ=C:\company folders\Documentation\INVENTORY.xls;', 'SELECT * FROM [Inventory$]')

AS DT

Anyone game in trying their hand?

I have also read the How to Pass a variable to a linked query, but have not gotten the quotations down right (','' )

Kind Regards,

Claudia.

|||

Take a look at your other post for a solution:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1587125&SiteID=17

Chris