{"id":5873,"date":"2016-12-24T02:05:23","date_gmt":"2016-12-24T02:05:23","guid":{"rendered":"http:\/\/www.jitendrazaa.com\/blog\/?p=5873"},"modified":"2016-12-24T02:35:51","modified_gmt":"2016-12-24T02:35:51","slug":"export-documents-saved-as-blob-binary-from-sql-server","status":"publish","type":"post","link":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/export-documents-saved-as-blob-binary-from-sql-server\/","title":{"rendered":"Export Documents saved as Blob \/ Binary from SQL Server"},"content":{"rendered":"

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\u00a0very well. Below\u00a0script was used to export around 25GB of files stored in SQL Server.<\/p>\n

To understand this, lets create a table in Database which will store files from local system into SQL Server as binary \/ Blob .<\/p>\n

\r\nCREATE TABLE [dbo].[Document](\r\n\t[Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,\r\n\t[Extension] [varchar](50) NULL,\r\n\t[FileName] [varchar](200) NULL,\r\n\t[Doc_Content] [varbinary](max) NULL\r\n) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]\r\n<\/pre>\n
How to Insert Blob into Database<\/h3>\n
For demo purpose, we will save two files in our table using below queries<\/p>\n
\r\nINSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )\r\nSELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*\r\nFROM OPENROWSET \r\n    (BULK 'C:\\G2\\My POC\\Blog\\SQL Server\\Source\\lightning.pdf', SINGLE_BLOB)  [Doc_Data]\r\n\r\nINSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )\r\nSELECT 'html', 'Progress.html',[Doc_Data].*\r\nFROM OPENROWSET \r\n    (BULK 'C:\\G2\\My POC\\Blog\\SQL Server\\Source\\Progress.html', SINGLE_BLOB)  [Doc_Data]\r\n<\/pre>\n
If we try to see content in actual table, it will look like<\/p>\n
\"Insert<\/a>
Insert Blob into Database<\/figcaption><\/figure>\n
Export Blob From SQL Server and save it as a file<\/h3>\n
For demo purpose, we want to save documents on local disc. We will use Doc_Num <\/em> 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<\/em> created in this post<\/a>.
\nNote : 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 <\/em>CreateFolder stored procedure for each folder. Its explained in this blog post<\/a>.<\/em><\/p>\n
Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.<\/p>\n
\r\nUSE [POC]\r\nDECLARE @outPutPath varchar(50) = 'C:\\G2\\My POC\\Blog\\SQL Server\\Extract Blob'\r\n, @i bigint\r\n, @init int\r\n, @data varbinary(max) \r\n, @fPath varchar(max)  \r\n, @folderPath  varchar(max) \r\n \r\n--Get Data into temp Table variable so that we can iterate over it \r\nDECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )\r\n \r\nINSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])\r\nSelect [Doc_Num] , [FileName],[Doc_Content] FROM  [dbo].[Document]\r\n \r\n--SELECT * FROM @table\r\n\r\nSELECT @i = COUNT(1) FROM @Doctable\r\n \r\nWHILE @i >= 1\r\nBEGIN \r\n\r\n\tSELECT \r\n\t @data = [Doc_Content],\r\n\t @fPath = @outPutPath + '\\'+ [Doc_Num] + '\\' +[FileName],\r\n\t @folderPath = @outPutPath + '\\'+ [Doc_Num]\r\n\tFROM @Doctable WHERE id = @i\r\n \r\n  --Create folder first\r\n  EXEC  [dbo].[CreateFolder]  @folderPath\r\n  \r\n  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created\r\n  EXEC sp_OASetProperty @init, 'Type', 1;  \r\n  EXEC sp_OAMethod @init, 'Open'; -- Calling a method\r\n  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method\r\n  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method\r\n  EXEC sp_OAMethod @init, 'Close'; -- Calling a method\r\n  EXEC sp_OADestroy @init; -- Closed the resources\r\n \r\n  print 'Document Generated at - '+  @fPath   \r\n\r\n--Reset the variables for next use\r\nSELECT @data = NULL  \r\n, @init = NULL\r\n, @fPath = NULL  \r\n, @folderPath = NULL\r\nSET @i -= 1\r\nEND\r\n<\/pre>\n
Variable @outPutPath<\/em> stores root folder path, where folders will be created and Blob content would be exported into it as a file.<\/p>\n
Below image shows output in action :<\/p>\n
\"SQL<\/a>
SQL Server Export Blob as File<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
T-SQL Scripts to Export Blob or Binary data stored in SQL Server<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"advanced_seo_description":"","jetpack_seo_html_title":"","jetpack_seo_noindex":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"jz_research_post":"","_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[14],"tags":[292],"class_list":["post-5873","post","type-post","status-publish","format-standard","hentry","category-sqlserver","tag-sql-server"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack-related-posts":[{"id":5882,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-read-all-files-in-directory-and-store-in-table\/","url_meta":{"origin":5873,"position":0},"title":"SQL Server – Read all files in directory and store in Table","author":"Jitendra","date":"January 18, 2017","format":false,"excerpt":"T-SQL Script to read all files in directory and store back in table - SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":5824,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/sql-server-create-folders-using-t-sql-ole-automation\/","url_meta":{"origin":5873,"position":1},"title":"SQL Server \u2013 Create Folders using T-SQL & OLE automation","author":"Jitendra","date":"November 24, 2016","format":false,"excerpt":"Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"SQL Server \u2013 Create Folders using T-SQL & OLE automation","src":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=700%2C400 2x, https:\/\/i0.wp.com\/www.jitendrazaa.com\/blog\/wp-content\/uploads\/2016\/11\/SQL-Server-%E2%80%93-Create-Folders-using-T-SQL-OLE-automation.png?fit=1200%2C580&ssl=1&resize=1050%2C600 3x"},"classes":[]},{"id":1422,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/update-from-statement-in-sql-server\/","url_meta":{"origin":5873,"position":2},"title":"“Update From” Statement in SQL Server","author":"Jitendra","date":"December 17, 2010","format":false,"excerpt":"Example to demonstrate the \"Update From\" Statement in SQL Server. This example can be used in scenarios where developer wants to update the Table with the help of Inner Join.","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"SQL Server Table 1","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/SQL-Server-Table-1.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":1376,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/use-of-openxml-in-sql-server\/","url_meta":{"origin":5873,"position":3},"title":"Use of OPENXML in SQL Server","author":"Jitendra","date":"December 4, 2010","format":false,"excerpt":"In SQL Server, OpenXML is very powerful method for the XML data manipulation. This article shows that how to use the OpenXML for XML string.","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"OpenXML in SQL Server","src":"https:\/\/i0.wp.com\/jitendrazaa.com\/blog\/wp-content\/uploads\/2010\/12\/OpenXML-in-SQL-Server.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":504,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/drop-function-if-exist-sql-server\/","url_meta":{"origin":5873,"position":4},"title":"Drop Function if exist – SQL Server","author":"Jitendra","date":"July 2, 2010","format":false,"excerpt":"Drop the user define function from database if it exists.","rel":"","context":"In "SQL"","block_context":{"text":"SQL","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":829,"url":"https:\/\/www.jitendrazaa.com\/blog\/sql\/sqlserver\/convert-rows-into-comma-separated-values-column-sql-server\/","url_meta":{"origin":5873,"position":5},"title":"Convert rows into comma separated values column – SQL Server","author":"Jitendra","date":"October 4, 2010","format":false,"excerpt":"Convert rows into comma separated values column - SQL Server","rel":"","context":"In "SQL Server"","block_context":{"text":"SQL Server","link":"https:\/\/www.jitendrazaa.com\/blog\/category\/sql\/sqlserver\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5873","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/comments?post=5873"}],"version-history":[{"count":2,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5873\/revisions"}],"predecessor-version":[{"id":5877,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/posts\/5873\/revisions\/5877"}],"wp:attachment":[{"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/media?parent=5873"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/categories?post=5873"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.jitendrazaa.com\/blog\/wp-json\/wp\/v2\/tags?post=5873"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}