I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.
To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .
CREATE TABLE [dbo].[Document]( [Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL, [Extension] [varchar](50) NULL, [FileName] [varchar](200) NULL, [Doc_Content] [varbinary](max) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
How to Insert Blob into Database
For demo purpose, we will save two files in our table using below queries
INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
FROM OPENROWSET
(BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB) [Doc_Data]
INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
FROM OPENROWSET
(BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB) [Doc_Data]
If we try to see content in actual table, it will look like
Export Blob From SQL Server and save it as a file
For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.
Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.
USE [POC] DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob' , @i bigint , @init int , @data varbinary(max) , @fPath varchar(max) , @folderPath varchar(max) --Get Data into temp Table variable so that we can iterate over it DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num] varchar(100) , [FileName] varchar(100), [Doc_Content] varBinary(max) ) INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content]) Select [Doc_Num] , [FileName],[Doc_Content] FROM [dbo].[Document] --SELECT * FROM @table SELECT @i = COUNT(1) FROM @Doctable WHILE @i >= 1 BEGIN SELECT @data = [Doc_Content], @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName], @folderPath = @outPutPath + '\'+ [Doc_Num] FROM @Doctable WHERE id = @i --Create folder first EXEC [dbo].[CreateFolder] @folderPath EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created EXEC sp_OASetProperty @init, 'Type', 1; EXEC sp_OAMethod @init, 'Open'; -- Calling a method EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method EXEC sp_OAMethod @init, 'Close'; -- Calling a method EXEC sp_OADestroy @init; -- Closed the resources print 'Document Generated at - '+ @fPath --Reset the variables for next use SELECT @data = NULL , @init = NULL , @fPath = NULL , @folderPath = NULL SET @i -= 1 END
Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.
Below image shows output in action :
Leave a Reply