Wednesday, March 2, 2011

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

1 comment:

  1. Hi,
    This is an excellent article on storing images in the database. I have a couple of questions.
    1) Is it possible in SQL server to create CRC values for these files using SHA256 algorithm?
    2) Also is there a way to resize the images in SQL server itself and store them as thumbnails in the same stored proc that you explained?

    Your help will be greatly appreciated.
    Thanks

    ReplyDelete

All suggestions are welcome