CODEWITHSUNDEEP
sqlsql-server
Taher
Taher

Reputation: 593

SQL Server : Insert Image Into Table Dynamically

I have a SQL Server table Products and I want to insert an image for each in the image column based on the product Code where the name of the image is identical to the product Code.

UPDATE Products
   SET ProdImage = (SELECT * FROM Openrowset
   ( Bulk 'C:\Users\DELL\Desktop\ImageCatalog\sbrk.jpg' , Single_Blob)
     as image)
 WHERE CODE = 'sbrk'
GO

The name of the image and the code is sbrk when I tried to make the path dynamic I get an exception:

Expecting STRING, TEXT_LEX

Upvotes: 0

Views: 1309

Answers (2)

mikeo
mikeo

Reputation: 1075

I believe to make the path dynamic you're going to need to use dynamic SQL. You can't just pass in a parameter into the BULK statement:

DECLARE @sql NVARCHAR(MAX)
DECLARE @Path NVARCHAR(MAX)
DECLARE @Code NVARCHAR(200)

SET @Path = 'C:\Users\DELL\Desktop\ImageCatalog\sbrk.jpg' 
SET @Code = 'sbrk'

SET @sql = '
UPDATE Products
   SET ProdImage = (SELECT * FROM Openrowset
   ( Bulk N''' + @Path + ''' , Single_Blob)
     as image)
 WHERE CODE = ''' + @Code + ''''

EXEC sp_executesql @sql

UPDATE

Turboc has it right (No rep to comment tho). I missed the part about the image file name being the same as the code:

    DECLARE @sql NVARCHAR(MAX)
    DECLARE @Path NVARCHAR(MAX)
    DECLARE @Code NVARCHAR(200)

    SET @Path = 'C:\Users\DELL\Desktop\ImageCatalog\' 
    SET @Code = 'sbrk'

    SET @sql = '
    UPDATE Products
   SET ProdImage = (SELECT * FROM Openrowset
   ( Bulk N''' + @Path +  @Code + '.jpg'' , Single_Blob)
     as image)
 WHERE CODE = ''' + @Code + ''''

 EXEC sp_executesql @sql

Upvotes: 1

turboc
turboc

Reputation: 153

I think the @Path statement given by darksolace may need to be modified to take out the code.jpg at the end.

SET @Path = 'C:\Users\DELL\Desktop\ImageCatalog\'

Then modify the update statement to append the code and the .jpg to the bulk subquery. I don't have sql here with me right now so the ''''''s might be off a little.

UPDATE Products
   SET ProdImage = (SELECT * FROM Openrowset
   ( Bulk N''' + @Path + @Code + '.jpg''' , Single_Blob)
     as image)
 WHERE CODE = ''' + @Code + ''''

But I think that with what darksolace recommended should get you pretty close.

Upvotes: 1

Related Questions