By: Nisarg Upadhyay | Updated: 2018-05-17 | Comments (3) | Related: > FILESTREAM
Problem
I need to import multiple documents and files into a FILESTREAM enabled database, so I built this SQL Server stored procedure to read contents from a directory and import the files into a FILESTREAM enabled table.
Solution
Recently I worked on a project to replace an existing third party internal support ticketing system to a new in-house developed support ticketing system. The old system was coded with images and documents uploaded by users stored onto a local drive of a web server. Now as we were planning a new setup we decided to change the file access approach.
The scenario is as follows:
- Among all documents, images and logs, 70% files are less than 3 MB in size.
- File access should be faster.
- There will be no change in the documents. The application is designed to download the files as needed.
We did some research to determine the best possible way to store the all files (I have added links in the Next Steps section). We were considering two options:
- Convert files in varbinary(max) datatype and store them in a SQL Server table.
- Convert files in varbinary(max) and store them in the file system using use the FILESTREAM option.
Considering performance benefits and space management, we decided to use FILESTREAM. To do this task more effectively, I developed a stored procedure to import various documents, images and log files from a specific directory.
Enable Filestream on SQL Server
Before developing the store procedure, we must enable FILESTREAM in SQL Server. Take a look at this article and this article for more information about how to do this.
I already enabled FILESTREAM on database and the location of the database FILESTREAM container is ‘E:\Support_Documents\DemoDatabase_FS’. In the filestream container, there will be two directories created named $FSGC and $FSLOG, which contains meta data for FILESTREAM. See the below image:
Enable Filestream for the SQL Server Database
I created a FILESTREAM enabled database filegroup named ‘DemoDatabase_FS’. The below query shows the FILESTREAM configuration:
USE demodatabase
go
SELECT NAME,
CASE
WHEN value = 0 THEN 'FILESTREAM is Disabled for this instance'
WHEN value = 1 THEN
'FILESTREAM is Enabled for Transact-SQL access for this instance'
WHEN value = 2 THEN
'FILESTREAM is Enabled for Transact-SQL and Win32 for this instance'
END AS FILESTREAM Option'
FROM sys.configurations
WHERE NAME = 'filestream access level'
go
SELECT NAME,
type_desc,
physical_name
FROM demodatabase.sys.database_files
Below is output of query:
Let me explain the stored procedure which will import various documents into the FILESTREAM enabled table.
Step 1 - Create table in database to store documents
Create a FILESTREAM enabled table ‘Tbl_Support_Documents’. This table will have a FILESTREAM column which is varbinary(max) with the FILESTRAM attribute enabled. Every FILESTREAM enabled table must have a UNIQUEIDENTIFIER column.
Use the below code to create a table which has a FILESTREAM column:
Create Table Tbl_Support_Documents
(
Document_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
Document_Name varchar(1000),
Document_Type varchar(50),
ImageBin varbinary (max) FILESTREAM NULL,
)
Step 2 - Create a stored procedure to import file stream data in table
This script will use a DOS command, so we must enable xp_cmdshell on the server. Execute the below code to enable xp_cmdshell.
use master go exec sp_configure 'show advanced option',1 reconfigure with override exec sp_configure 'xp_cmdshell',1 reconfigure with override
To insert multiple documents in a table, first create a temp table to store the path of the directory where the files are located. Then create a dynamic SQL statement which executes a DIR command to get a list of files. The output of the query will be stored in temp table (#Document_Name). The below code will perform this task:
CREATE TABLE #Document_Name
(
id INT IDENTITY (1, 1) PRIMARY KEY,
Document_Name VARCHAR(2000)
)
DECLARE @SQLCommand VARCHAR(4000)
SET @SQLCommand = LEFT('dir "' + @DirectoryLocation + '" /A-D /B ' + @order, 4000)
INSERT INTO #Document_Name (Document_Name)
EXECUTE xp_cmdshell @CommandLine
Create another temp table named #Final_Document. In this table, we will store the file name, full path of file and file description based on the file extension. In the code, to determine the file type I have used a case statement. The below code will perform this task:
CREATE TABLE #Final_Documents
(
id INT IDENTITY (1, 1) PRIMARY KEY,
[Document Name] VARCHAR(max),
[Document Type] VARCHAR(max),
[Directory Location] VARCHAR (50)
)
INSERT INTO #Final_Documents
(Document_Name,
Document_Type,
Directory_Location)
SELECT Document_Name,
CASE
WHEN (Document_Name LIKE '%jpg%')
OR (Document_Name LIKE '%png%')
OR (Document_Name LIKE '%jpg%')
OR (Document_Name LIKE '%bmp%') THEN 'Image Files'
WHEN (Document_Name LIKE '%txt%')
OR (Document_Name LIKE '%rtf%')
OR (Document_Name LIKE '%xls%')
OR (Document_Name LIKE '%doc%') THEN 'Documents'
ELSE 'Other Files'
END AS 'File Type',
@DirectoryLocation AS 'File Location'
FROM #DocumentName
Output of #Final_Document table will look like the below image:
Finally insert all documents into the table. To do that we will use a dynamic query which iterates through the #Final_Document table, populate the value of directorypath column of the #Final_Document table, use OPENROWSET(Bulk) to load the contents of the document from disk to the varbinary(max) column and then it insert it to the FILESTRAM enabled column Tbl_Support_Documents. See the below code:
DECLARE @FileCount INT
DECLARE @I INT = 0
DECLARE @FileName NVARCHAR(max)
DECLARE @SQLText NVARCHAR(max)
DECLARE @FileLocation NVARCHAR(max)
SET @FileCount = (SELECT Count(*)
FROM #Final_Document)
WHILE ( @i < @FileCount )
BEGIN
/* Get the File Name from #Final_Document table. */
SET @FileName = (SELECT TOP 1 filename
FROM #Final_Document)
/* Concate DirectoryLocation and FileName column to generate FQDN. */
SET @FileLocation = (SELECT TOP 1 @DirectoryLocation + '\' +@FileName
FROM #Final_Document)
SET @SQLText = 'Insert into Tbl_Support_Documents (Document_ID, Document_Name, Document_Type, DocumentBin) Select NEWID(),''' + @FileName
+
''',(select filetype from #Final_Document where FileName='''
+ @FileName
+ '''), BulkColumn from Openrowset(Bulk '''
+ @FileLocation + ''', Single_Blob) as tb'
EXEC Sp_executesql @SQLText
DELETE FROM #Final_Document WHERE [filename] = @FileName
SET @I = @I + 1
END
The entire script is below:
--/*This script will insert multiple image files in SQL Server using OPENROWSET command*/
/*
Author: Nisarg Upadhyay
How to execute:
Use DemoDatabase
Go
Execute Importfiles @DirectoryLocation= ‘E:\Documents’
*/
USE demodatabase
go
/* Step 1: Create table to Store documents*/
If exists (select name from sys.tables where name='Tbl_Support_Documents')
drop table Tbl_Support_Documents
Go
Create Table Tbl_Support_Documents
(
Document_ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
Document_Name varchar(1000),
Document_Type varchar(50),
DocumentBin varbinary (max) FILESTREAM NULL,
)
Go
/*Step 2 : Create procedure*/
If exists (select name from sys.procedures where name='Importfiles')
drop procedure Importfiles
Go
CREATE PROCEDURE Importfiles @DirectoryLocation VARCHAR(max)
AS
BEGIN
CREATE TABLE #Final_Document
(
id INT IDENTITY (1, 1) PRIMARY KEY,
Document_Name VARCHAR(max),
Document_Type VARCHAR(max),
Directory_Location VARCHAR(50)
)
DECLARE @FileSpec VARCHAR(2000)
DECLARE @order VARCHAR(80) = '/O-D' --Sort file Names
CREATE TABLE #Document_Name
(
id INT IDENTITY (1, 1) PRIMARY KEY,
Document_Name VARCHAR(2000)
)
DECLARE @SQLCommand VARCHAR(4000)
SET @SQLCommand = LEFT('dir "' + @DirectoryLocation + '" /A-D /B ' + @order, 4000)
INSERT INTO #Document_Name (Document_Name)
EXECUTE xp_cmdshell @SQLCommand
INSERT INTO #Final_Document
([Document_Name],
[Document_Type],
[Directory_Location])
SELECT Document_Name,
CASE
WHEN ( Document_Name LIKE '%jpg%' )
OR ( Document_Name LIKE '%png%' )
OR ( Document_Name LIKE '%jpg%' )
OR ( Document_Name LIKE '%bmp%' ) THEN 'Image Files'
WHEN ( Document_Name LIKE '%txt%' )
OR ( Document_Name LIKE '%rtf%' )
OR ( Document_Name LIKE '%xls%' )
OR ( Document_Name LIKE '%doc%' ) THEN 'Documents'
ELSE 'Other Files'
END AS 'File Type',
@DirectoryLocation AS 'File Location'
FROM #Document_Name
DECLARE @FileCount INT
DECLARE @I INT = 0
DECLARE @FileName NVARCHAR(max)
DECLARE @SQLText NVARCHAR(max)
DECLARE @FileLocation NVARCHAR(max)
SET @FileCount = (SELECT Count(*) FROM #Final_Document)
WHILE ( @i < @FileCount )
BEGIN
/* Get the File Name from #Final_Document table.*/
SET @FileName = (SELECT TOP 1 Document_Name
FROM #Final_Document)
/* Concate DirectoryLocation and FileName column to generate FQDN.*/
SET @FileLocation = (SELECT TOP 1 @DirectoryLocation + '\' +@FileName
FROM #Final_Document)
SET @SQLText = 'Insert into Tbl_Support_Documents(Document_ID,Document_Name, Document_Type, DocumentBin) Select NEWID(),''' + @FileName
+
''',(select Document_Type from #Final_Document where Document_Name ='''
+ @FileName
+ '''), BulkColumn from Openrowset( Bulk '''
+ @FileLocation + ''', Single_Blob) as tb'
EXEC Sp_executesql @SQLText
DELETE FROM #Final_Document
WHERE Document_Name = @FileName
SET @I = @I + 1
END
END
Step 3 - Load the documents and files
Execute the script as follows to load the files that are in the specified folder:
USE DemoDatabase GO Execute Importfiles @DirectoryLocation = 'E:\Documents'
Step 4 - Verify that files has been stored in the table
Now let’s verify that the files have been saved in the FILESTRAM container. To do that, we need to identify the logical path which uniquely identifies the files stored in the FILESTRAM data source. So, we will use PathName () method of file-stream column. Execute the below code to retrieve the pathname associated with the file stream data.
SELECT Document_Name, DocumentBin.PathName() as 'File Location' FROM Tbl_Support_Documents WHERE document_name='Demo_Script.sql'
Below is the query output:
Now let’s navigate to the FILESTREAM data container (‘E:\Support_Documents\DemoDatabase_FS’) to verify that files have been created.
See below screenshot:
Summary
In this tip we covered the following:
- Explained a real-world scenario to determine when to use the FILESTREAM option.
- Explained the stored procedure to import various documents and files into a FILESTREAM enabled database.
Next Steps
- Check out these related articles:
About the author
Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional with more than 5 years of experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2018-05-17
