Search This Blog

SQL Query Import Images

We use Exact Globe Next, and with this query I can put the product images on a networkshare, map it to the database server. 
And it will "update" all the images in the database, based on the itemcode (productnumber) of the photo.

USE [901] -- administration number
DECLARE @itemcode varchar(max)
DECLARE image_cursor CURSOR FOR
SELECT ItemCode FROM Items WHERE IsSalesItem = 1 AND syscreated > '2013-01-01' -- criteria to match images against.
 OPEN image_cursor;
 
FETCH NEXT FROM image_cursor
 INTO @itemcode;

WHILE @@FETCH_STATUS = 0
 BEGIN
DECLARE @sql VARCHAR(MAX)
DECLARE @clean VARCHAR(MAX)
DECLARE @imagePath VARCHAR(255)
DECLARE @image VARCHAR(255)
SET @image = RTRIM(LTRIM(@itemcode)) + '.jpg'
SET @imagePath = 'Y:\' + @image

SET @sql = 'UPDATE Items SET Picture = (SELECT * FROM OPENROWSET(BULK ''' + @imagePath + ''', SINGLE_BLOB) AS img), PictureFileName=''' + @image + ''' WHERE ItemCode = ''' + @itemcode + ''''
--PRINT @sql
BEGIN
EXEC(@sql)
END
FETCH NEXT FROM image_cursor
 INTO @itemcode;
 END
CLOSE image_cursor;
DEALLOCATE image_cursor;
 
SELECT ItemCode, Picture, PictureFilename FROM Items
WHERE Picture IS NOT NULL