SQL: Update table with a picture from folder, by id #

Question

I have a table at the moment with columns ( id#(int), picture(varbinary(MAX) ), and I have a folder on my HDD with a ton of pictures. The pictures in the folder are named by the 'id' I want to match them with in my table. How can I do this?

Example:

Table Row: id=25166, picture=NULL
25166.jpg

Answer 1

If you need an example which uses just SQL checkout the following:

It uses a cursor to loop through each of the rows which do not contain image data and for each row found uses the OPENROWSET BULK provider to load the file.

CREATE TABLE ImageStore
( 
id INT, 
picture VARBINARY(MAX) NULL
)
GO 

INSERT INTO ImageStore (id) VALUES(25166)
INSERT INTO ImageStore (id) VALUES(25167)

DECLARE @id INT
DECLARE image_cursor CURSOR FOR 
SELECT id FROM ImageStore WHERE picture IS NULL

OPEN image_cursor;

FETCH NEXT FROM image_cursor 
INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX)

    DECLARE @imagePath NVARCHAR(255)
    SET @imagePath = 'C:\' + RTRIM(LTRIM(STR(@id))) + '.gif'

    SET @sql = 'UPDATE ImageStore '
    SET @sql = @sql + 'SET picture = (SELECT BulkColumn FROM OPENROWSET( BULK ''' + @imagePath + ''', Single_Blob) AS picture) '
    SET @sql = @sql + 'WHERE id = ' + STR(@id)

    BEGIN TRY
        EXECUTE sp_executesql @sql 
    END TRY
    BEGIN CATCH

    END CATCH   

    FETCH NEXT FROM image_cursor 
    INTO @id;
END
CLOSE image_cursor;
DEALLOCATE image_cursor;

SELECT * FROM ImageStore

DROP TABLE ImageStore