SQL Server: How to Load Files From Given Directory To a Table

Recently a reader asked that how he can read his image files names, which are placed in a folder of operating system and how can he insert these images from this path, directly into a database table.

Following is the script, which will help you to read a file name from operating system, and later on insert these files one by one in a database table. For following script I have placed my target picture files at D:\ SamplePictures.
(Note: How to enable xp_cmdshell CLICK HERE)

Extended stored procedure xp_cmdshell will enable us to execute execute a shell command like DIR

USE AdventureWorks
GO 
 
IF OBJECT_ID('MyPictures', 'U') IS NOT NULL 
BEGIN
DROP TABLE dbo.MyPictures 
END
CREATE TABLE dbo.MyPictures
(
PicId INT IDENTITY(1, 1),
PicName VARCHAR(50),
OrignalFileName VARCHAR(50),
CreationDate DATE,
Picture VARBINARY(MAX)
) 
 
DECLARE @PathName VARCHAR(256), -- to hold folder path where pictures are placed
@CMD VARCHAR(512), -- to hold command, we will use DIR (Directory) as command
@SQLstr VARCHAR(1000)-- to hold generic query text 
 
-- Temp table to hold intermediate results
CREATE TABLE #CmdShell ( Title VARCHAR(512) ) 
SET @PathName = 'D:\SamplePictures\*.*' 
SET @CMD = 'DIR ' + @PathName + ' /TC' 
 
-- Populate temp table by reading file names,through xp_cmdshell, from given folder
INSERT  INTO #CmdShell
EXEC MASTER..xp_cmdshell @CMD 
 
-- Delete records other then file names
DELETE  FROM #CmdShell
WHERE   Title NOT LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9] %'
OR Title LIKE '%<DIR>%'
OR Title IS NULL 
 
-- Cursor is used to insert records, with picture file 
DECLARE CUR_IMG CURSOR FAST_FORWARD
FOR SELECT  Title AS OrignalFileName,
REVERSE(LEFT(REVERSE(Title),
CHARINDEX(' ', REVERSE(Title)) - 1)) AS FileName,
LEFT(Title, 10) AS CreationDate
FROM    #CmdShell        
OPEN CUR_IMG 
DECLARE @OrignalFileName VARCHAR(50),
@FileName VARCHAR(50),
@CreationDate DATETIME
FETCH NEXT FROM CUR_IMG INTO @OrignalFileName, @FileName, @CreationDate 
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
SELECT  @SQLstr = 'INSERT INTO dbo.MyPictures (PicName,OrignalFileName,CreationDate,Picture)
SELECT ''' + @OrignalFileName + ''',
''' + @FileName + ''',
'''
+ CAST(@CreationDate AS VARCHAR(50))
+ ''', 
* 
FROM OPENROWSET(BULK ''D:\SamplePictures\'
+ @FileName + ''', SINGLE_BLOB) AS imagesource'
EXEC ( @SQLstr
)
FETCH NEXT FROM CUR_IMG INTO @OrignalFileName, @FileName,
@CreationDate 
END
CLOSE CUR_IMG
DEALLOCATE CUR_IMG
GO 
 
-- drop temporary table when not required
DROP TABLE #CmdShell