SQL Server 2016 Hosting – HostForLIFE.eu :: SQL Script To Insert Images, Document In Table

Peter Cole | August 3, 2017 | SQL 2016 Hosting | No Comments

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   
      
    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

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

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 '''[email protected]+''',''Files'', BulkColumn from Openrowset( Bulk '''[email protected]+''', Single_Blob) as tb' --Here Instead of "Files" you can add

exec @SQLText

delete from #TempFileList where [email protected]

set @I=@I+1

End

drop table #TempFileList

Related Posts

About The Author

Pete