skip to Main Content

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]

About the Author

Geoff Ballard

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).

 

Back To Top