My task is to convert jpeg's to binary and then insert them into a table called "images". I need to convert/insert all jpeg files in a directory. I'm able to accomplish the task if the files are numbered. The query below works by retrieving one file at a time based on the value of @i. However, I also have directories where the files are not numbered but have ordinary text names like "Red_Sofa.jpg". I need to iterate through these directories as well and convert/insert the jpeg's. I'm running SSMS 2014 Express on 4.0 and Windows 7. I appreciate the help.

DECLARE @i int
SET @i = 951
WHILE (@i <= 951)
BEGIN
    DECLARE @SQL varchar(MAX)
    SELECT @SQL = 
        'INSERT INTO images (image_name, image_data)
        SELECT 
        ' + convert(nvarchar(5), @i) + ' AS image_name, 
        BulkColumn FROM OpenRowSet ( Bulk ''C:\DB\' + 
        convert(nvarchar(5), @i) + 
        '.jpg'', Single_Blob) AS image_data'

    EXEC (@SQL)
    SET @i = @i + 1
END
GO

Refer the below sql, we can collect the file name into a temporary table and use in the subsequent statement to convert into the image into binary data.

	DECLARE @folder varchar(500) = 'C:\testing'
	DECLARE @DOSCommand varchar(1024); SET @DOSCommand =  'dir ' + @folder +'\'  + ' /A-D  /B' 
	IF OBJECT_ID('tempdb..#myFileList') IS NOT NULL DROP TABLE #myFileList
	CREATE TABLE #myFileList (FileNumber INT IDENTITY,[FileName] VARCHAR(256)) 
	INSERT INTO #myFileList([FileName]) EXEC MASTER.dbo.xp_cmdshell @DOSCommand 

	SELECT * FROM #myFileList where fileName is not null

	DROP TABLE #myFileList		

Hi Rajen: Thanks so much for your help. I was starting to think about a table of file names as well -- makes sense.  But I'm not clear how my code works with yours? Can you take a bit further?

Try something like this. Please test it.

DECLARE @folder varchar(500) = 'C:\testing'
	DECLARE @DOSCommand varchar(1024); SET @DOSCommand =  'dir ' + @folder +'\'  + ' /A-D  /B' 
	IF OBJECT_ID('tempdb..#myFileList') IS NOT NULL DROP TABLE #myFileList
	CREATE TABLE #myFileList (FileNumber INT IDENTITY,[FileName] VARCHAR(256)) 
	INSERT INTO #myFileList([FileName]) EXEC MASTER.dbo.xp_cmdshell @DOSCommand 

	SELECT * FROM #myFileList where fileName is not null

	---------------
	DECLARE @i int, @filename varchar(200)
	SET @i = 1
	WHILE (@i <= (select max(FileNumber) from #myFileList))
	BEGIN
		DECLARE @SQL varchar(MAX)
		SELECT @filename= [FileName] from #myFileList WHERE FileNumber = @i
		SELECT @SQL = 
			'INSERT INTO images (image_name, image_data)
			SELECT 
			' + convert(nvarchar(5), @i) + ' AS image_name, 
			BulkColumn FROM OpenRowSet ( Bulk ''C:\DB\' + @filename + ', Single_Blob) AS image_data'

		PRINT (@SQL)
		SET @i = @i + 1
	END
	GO
	----------------------------

	DROP TABLE #myFileList

Here's how the code now looks. Messages results at the bottom. It appears that my test file 'Artwork.jpg' is getting processed, however, it doesn't show in the 'images' table.

use TestStaging;
go

DECLARE @folder varchar(500) = 'C:\Users\Public'
DECLARE @DOSCommand varchar(1024);
SET @DOSCommand =  'dir ' + @folder +'\'  + ' /A-D  /B'

IF OBJECT_ID('tempdb..#myFileList') IS NOT NULL DROP TABLE #myFileList
CREATE TABLE #myFileList (FileNumber INT IDENTITY,[FileName] VARCHAR(256))
INSERT INTO #myFileList([FileName]) EXEC MASTER.dbo.xp_cmdshell @DOSCommand

SELECT * FROM #myFileList where fileName is not null

------------------------------
    DECLARE @i int, @filename varchar(200)
    SET @i = 1
    WHILE (@i <= (select max(FileNumber) from #myFileList))
    BEGIN
        DECLARE @SQL varchar(MAX)
        SELECT @filename= [FileName] from #myFileList WHERE FileNumber = @i
        SELECT @SQL =
            'INSERT INTO images (image_name, image_data)
            SELECT
            ' + convert(nvarchar(5), @i) + ' AS image_name,
            BulkColumn FROM OpenRowSet ( Bulk ''C:\Users\Public\' + @filename + ', Single_Blob) AS image_data'

        PRINT (@SQL)
        SET @i = @i + 1
    END
    GO
------------------------------

DROP TABLE #myFileList

select *
from images
;
go

Results:

FileNumber    FileName
1    Artwork.jpg
2    desktop.ini

image_name image_data

--no results

Messages:
(3 row(s) affected)

(2 row(s) affected)
INSERT INTO images (image_name, image_data)
            SELECT
            1 AS image_name,
            BulkColumn FROM OpenRowSet ( Bulk 'C:\Users\Public\Artwork.jpg, Single_Blob) AS image_data
INSERT INTO images (image_name, image_data)
            SELECT
            2 AS image_name,
            BulkColumn FROM OpenRowSet ( Bulk 'C:\Users\Public\desktop.ini, Single_Blob) AS image_data

Update the statement "PRINT (@SQL)" to "Exec (@SQL)"

use TestStaging;
go

DECLARE @folder varchar(500) = 'C:\Users\Public'
DECLARE @DOSCommand varchar(1024);
SET @DOSCommand =  'dir ' + @folder +'\'  + ' /A-D  /B'

IF OBJECT_ID('tempdb..#myFileList') IS NOT NULL DROP TABLE #myFileList
CREATE TABLE #myFileList (FileNumber INT IDENTITY,[FileName] VARCHAR(256))
INSERT INTO #myFileList([FileName]) EXEC MASTER.dbo.xp_cmdshell @DOSCommand

SELECT * FROM #myFileList where fileName is not null

------------------------------
    DECLARE @i int, @filename varchar(200)
    SET @i = 1
    WHILE (@i <= (select max(FileNumber) from #myFileList))
    BEGIN
        DECLARE @SQL varchar(MAX)
        SELECT @filename= [FileName] from #myFileList WHERE FileNumber = @i
        SELECT @SQL =
            'INSERT INTO images (image_name, image_data)
            SELECT
            ' + @filename + ' AS image_name,
            BulkColumn FROM OpenRowSet ( Bulk ''C:\Users\Public\' + @filename + ', Single_Blob) AS image_data'

        EXEC (@SQL)
        SET @i = @i + 1
    END
    GO
------------------------------

DROP TABLE #myFileList

select *
from images
;
go		

Made the change, getting new error. I've triple-checked the syntax and I just can't see any mismatches.

(3 row(s) affected)

(2 row(s) affected)
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 'C:\Users\Public\Artwork.jpg, Single_Blob) AS image_data'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'C:\Users\Public\Artwork.jpg, Single_Blob) AS image_data'.
Msg 105, Level 15, State 1, Line 6
Unclosed quotation mark after the character string 'C:\Users\Public\desktop.ini, Single_Blob) AS image_data'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'C:\Users\Public\desktop.ini, Single_Blob) AS image_data'.

(0 row(s) affected)

Try this,

use TestStaging;
go

DECLARE @folder varchar(500) = 'C:\Users\Public'
DECLARE @DOSCommand varchar(1024);
SET @DOSCommand =  'dir ' + @folder +'\'  + ' /A-D  /B'

IF OBJECT_ID('tempdb..#myFileList') IS NOT NULL DROP TABLE #myFileList
CREATE TABLE #myFileList (FileNumber INT IDENTITY,[FileName] VARCHAR(256))
INSERT INTO #myFileList([FileName]) EXEC MASTER.dbo.xp_cmdshell @DOSCommand

SELECT * FROM #myFileList where fileName is not null

------------------------------
    DECLARE @i int, @filename varchar(200)
    SET @i = 1
    WHILE (@i <= (select max(FileNumber) from #myFileList))
    BEGIN
        DECLARE @SQL varchar(MAX)
        SELECT @filename= [FileName] from #myFileList WHERE FileNumber = @i
        SELECT @SQL =
            'INSERT INTO images (image_name, image_data)
            SELECT ''' + @filename + ''' AS image_name,
            BulkColumn FROM OpenRowSet ( Bulk ''C:\Users\Public\' + @filename + ''', Single_Blob) AS image_data'

        Exec(@SQL)
        SET @i = @i + 1
    END
    GO
------------------------------

DROP TABLE #myFileList

select *
from images
;
go		

Working! I very much appreciate  your help, Rajen. Thanks again.

Tim