In this article, I will demonstrate how to insert an Excel file in SQL table.

Scenario

1. We need to store a few Excel files in a SQL Server table. 
2. All files are located at one location. (E:\ExcelOutput)
3. All files should be inserted in single batch

To handle this scenario, I had developed a script which will import all Excel files in a table. All files are in the same location, so I have used the xp_dirtree and “OpenRowset" commands. The XP_Dirtree is an undocumented command that is used to get the directory structure. More Information on "XP_Dirtree" is here. The OpenRowset command is used to perform a bulk import of varchar(max), nvarchar(max) and varbinary(max). More information on "OpenRowset" Command is here.

Now to accomplish our task.  First we will create three tables: filelist, #temptable, and TestBlob.

1. In the #TempTable, we will save file Name.
2. In the FileList table, we will save file name with path, e.g E:\ExcelOutput\BIGINT.xlsx .
3. In the TestBlob table, we will save all the image files converted to a blob data type.

Here is the code to create the tables.

create table FileList       -- Table to store files 
( 
    id int identity(1,1) primary key clustered, 
    FileName varchar(max) 
) 
create Table #TempTable     -- Table to store output of xp_dirtree 
( 
    id int identity(1,1) primary key clustered, 
    FileName varchar(max), 
    FileDepth int, 
    FileID int 
) 
 
CREATE TABLE  dbo.TestBlob  -- Table where BLOB will be stored 
( 
     tbId   int  IDENTITY(1,1) NOT NULL, 
     tbName   varchar (50) NULL, 
     tbDesc   varchar (100) NULL, 
     tbBin   varbinary (max) NULL 
)  

Now we will insert the names of the Excel files, which are in the "E:\ExcelOutput" directory. We use xp_dirtree to get the filename and insert it into #TempTable.

insert into #TempTable   EXEC master.sys.xp_dirtree 'E:\ExcelOutput',0,1;
  
select * from #TempTable

This is shown in SSMS below.

Now we will save the full path in the FileList table. To do that I have written a dynamic SQL query, which will generate a string by selecting the fileName column from #TempTable and the static directory path, E:\ExcelOutput. The string will look like “E:\ExcelOutput\BIGINT.xlsx”. The query will insert the string (Path + FileName) into the  FileList table.

insert into FileList (FileName) select 'E:\Scripts\' + Filename from #TempTable  
select * from FileList 

Now we will perform an insert operation to store the images in the TestBlob table. To do that we have to create a dynamic SQL query that will select the value from the filename column in the FileList table and save the value in the #TempFileList table. The dynamic SQL query will generate another query that converts the retreive the file location of the Excel file, convert it to a varbinary(max) datatype and insert it into the TestBlob table.

The insert stament generated by the dynamic query will look like this:

Insert TestBlob(tbName, tbDesc, tbBin) Select 'E:\ExcelOutput\BIGINT.xlsx','Files',
    BulkColumn from Openrowset( Bulk 'E:\ExcelOutput\BIGINT.xlsx', Single_Blob) as tb
Here is the code to insert the data.

declare @I int =0  
declare @FileName varchar(max)  
declare @Count int  
/* Save File name in #TempFileList Table*/select * into #TempFileList from  FileList  
set @Count=(select count(*) from #TempFileList)  
/*Generate Dynamic sql statement to convert physical file in Single_Blob and store it in TestBlob table */declare @SQLText nvarchar(max)  
While (@i<@Count)  
begin  
    set @FileName=(select top 1 FileName from #TempFileList)  
    set @SQLText='Insert TestBlob(tbName, tbDesc, tbBin) Select '''+@FileName+''',''Files'',
    BulkColumn from Openrowset( Bulk '''+@FileName+''', Single_Blob) as tb'   
    exec @SQLText  
    delete from #TempFileList where FileName=@FileName  
    set @I=@I+1  
End

Let's verify the output

select tbID as ID,tbName as 'File Name', tbBin as 'Converted file'  from TestBlob

You can see this in SSMS.

Conclusion

This article will be helpful to the developers / DBAs who want to store all files in a SQL server table within a single batch.  Performance of the script is based on the size of file. As we are storing object / files in SQL tables, we need to consider the table growth and plan database size accordingly.