>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



Advertisements

Posted on March 2, 2011, in Sql Server 2005, Sql Server 2008, TSQL Tips n Tricks. Bookmark the permalink. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: