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
  3. Author: Nisarg Upadhyay
  4. Description: This script will perform bulk insert of BLOB files in SQL Table.
  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. )
  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. )
  29. insert into #TempTable EXEC master.sys.xp_dirtree 'E:\Scripts',0,1;
  30. insert into FileList (FileName) select 'E:\Scripts\' + Filename from #TempTable
  33. /*Bulk Insert Files in database*/
  35. declare @I int =0
  36. declare @FileName varchar(max)
  37. declare @Count int
  39. select * into #TempFileList from FileList
  40. set @Count=(select count(*) from #TempFileList)
  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