Post
Ask Question

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

  1. /*Script to convert Files in varbinary and save in table   
  2.   
  3. Author: Nisarg Upadhyay  
  4. Description: This script will perform bulk insert of BLOB files in SQL Table.  
  5.   
  6. */  
  7. set nocount on  
  8. create table FileList       -- Table to store files  
  9. (  
  10.     id int identity(1,1) primary key clustered,  
  11.     FileName varchar(max)  
  12. )  
  13. create Table #TempTable     -- Table to store output of xp_dirtree  
  14. (  
  15.     id int identity(1,1) primary key clustered,  
  16.     FileName varchar(max),  
  17.     FileDepth int,  
  18.     FileID int  
  19. )  
  20.   
  21. CREATE TABLE  dbo.TestBlob  -- Table where BLOB will be stored  
  22. (  
  23.      tbId   int  IDENTITY(1,1) NOT NULL,  
  24.      tbName   varchar (50) NULL,  
  25.      tbDesc   varchar (100) NULL,  
  26.      tbBin   varbinary (max) NULL  
  27. )      
  28.   
  29. insert into #TempTable   EXEC master.sys.xp_dirtree 'E:\Scripts',0,1;  
  30. insert into FileList (FileName) select 'E:\Scripts\' + Filename from #TempTable  
  31.   
  32.   
  33. /*Bulk Insert Files in database*/  
  34.   
  35. declare @I int =0  
  36. declare @FileName varchar(max)  
  37. declare @Count int  
  38.   
  39. select * into #TempFileList from  FileList  
  40. set @Count=(select count(*) from #TempFileList)  
  41.   
  42. declare @SQLText nvarchar(max)  
  43. While (@i<@Count)  
  44. begin  
  45.     set @FileName=(select top 1 FileName from #TempFileList)  
  46.     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   
  47.     exec @SQLText  
  48.     delete from #TempFileList where FileName=@FileName  
  49.     set @I=@I+1  
  50. End   
  51. drop table #TempFileList