Loading Images Into SQL Server

A requirement arose recently to import a folder full of JPEG images into a SQL Server table. I ideally wanted to achieve this using pure T-SQL code; that is without using CLR code, external stored procedures or references to OLE objects.

The first challenge was to find a way to obtain a list of all the files in a particular operating system folder. To do this I decided to use xp_cmdshell to execute the DIR *.jpg command. The results returned from the EXEC statement can then be stored in a database table using the INSERT … EXEC statement. The required code is:

INSERT INTO ImportedImages(FileName)
EXEC xp_cmdshell 'dir c:\work\images\*.jpg /B'

DELETE FROM ImportedImages
WHERE FileName IS NULL

The DELETE is used to necessary to clear out a NULL file name returned by DIR.

The next challenge is, given a file name, to import the contents of the file into a column in a record in the database. The OPENROWSET function has a BULK option that can do this and the required command is:

SELECT bulkcolumn
FROM OPENROWSET(BULK 'C:\work\Images\1001.jpg', SINGLE_BLOB) AS I

This can be combined with an update to directly update the image column in the database as follows:

UPDATE ImportedImages

SET ImageData = (SELECT bulkcolumn
FROM OPENROWSET(BULK 'C:\work\Images\1001.jpg', SINGLE_BLOB) AS I)
WHERE FileName = 'C:\work\Images\1001.jpg'

The rest of the code just requires a cursor to loop through each file name and load the images one by one using dynamic SQL.

The full code including creating the database table is:

-- Create the table to hold the images

CREATE TABLE ImportedImages(
      FileName sysname NULL, ImageData varbinary(max) NULL)
GO

-- Get images names from folder
INSERT INTO ImportedImages(FileName)
EXEC xp_cmdshell 'dir c:\work\images\*.jpg /B'

DELETE FROM ImportedImages

WHERE FileName IS NULL
GO

-- Import all impages from folder

DECLARE C CURSOR FOR
      (SELECT FileName FROM ImportedImages WHERE ImageData IS NULL)
DECLARE @FileName sysname
DECLARE @Path sysname
DECLARE @SQL varchar(max)

-- Loop through each file loading the images one by one

OPEN C
FETCH NEXT FROM C INTO @FileName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @Path = 'C:\work\Images\' + @FileName
  SET @SQL =
  'UPDATE ImportedImages
      SET ImageData = (SELECT bulkcolumn FROM OPENROWSET(
      BULK ''' + @Path + ''', SINGLE_BLOB) AS IMAGE)
      WHERE FileName = ''' + @FileName + ''''
  EXEC (@sql)
  FETCH NEXT FROM C INTO @FileName
END
CLOSE C
DEALLOCATE C