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

 

By Geoff Ballard, Chief Technical Officer

 

Have a comment? Let us know below, or send an email to [email protected]