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
|