Stored procedure to import files into a SQL Server FILESTREAM enabled table

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:

Resources

Community

Subscribe