SQL Script To Insert Images, Document In Table

Recently, I was assigned a very interesting project. I needed to insert all the document files and images in SQL table. In SQL, using BLOB data type, we can store various files in table. Now, we have a total of 250 files in one directory and I have accomplished this project without development team help.

To insert files in table, we can use “OpenRowset (Bulk, Single_Blob)”. You can find detailed syntax here. Now, as I have to insert 250 files in a single effort, I developed a dynamic script. All files are in one directory so it becomes very easy to do this using xp_dirtree.

Below is script

/*Script to convert Files in varbinary and save in table

Author: Nisarg Upadhyay
Description: This script will perform bulk insert of BLOB files in SQL Table.

*/
set nocount on
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
)

insert into #TempTable   EXEC master.sys.xp_dirtree 'E:\Scripts',0,1;
insert into FileList (FileName) select 'E:\Scripts\' + Filename from #TempTable


/*Bulk Insert Files in database*/

declare @I int =0
declare @FileName varchar(max)
declare @Count int

select * into #TempFileList from  FileList
set @Count=(select count(*) from #TempFileList)

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'  --Here Instead of "Files" you can add
    exec @SQLText
    delete from #TempFileList where FileName=@FileName
    set @I=@I+1
End
drop table #TempFileList