SQL Server 2016 Hosting – HostForLIFE.eu :: 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
/*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 set @I=@I+1 End drop table #TempFileList |