How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server
Certifications:
In SQL Server we have the ability to store files such as Docx, Xlsx, XML, JSON, JPG, PNG etc. in a field of a database table. In this article we will analyze how this is done and how it is done the other way around we export these files from the database to a folder in the file system.
How do we import the files from a folder into the table
To begin with, we will have to make the table in which we will insert them. In this table we will define an identity field with sequential numbering, the file name, the file type and finally a type field varbinary(max) which will contain the entire file in binary form.
create table mig_db.dbo.docs ( id int identity(1,1), docname nvarchar(200), filetype varchar(100), document varbinary(max) ); alter table mig_db.dbo.docs add constraint pk_docs primary key clustered(id);
The files we want to upload are located in the following folder on our server:
All we have to do now is load in the array with insert one by one the files we want. The field that will contain the file is loaded as blob (binary large object) directly with the known function openrowset, as shown in the following examples:
insert into mig_db.dbo.docs select N'doc1.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc1.docx', single_blob) as lob; insert into mig_db.dbo.docs select N'doc2.docx','.docx',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc2.docx', single_blob) as lob; insert into mig_db.dbo.docs select N'doc3.doc','.doc',bulkcolumn from openrowset (bulk N'c:\SQL2019\doc3.doc', single_blob) as lob; insert into mig_db.dbo.docs select N'dp_logo.jpg','.jpg',bulkcolumn from openrowset (bulk N'c:\SQL2019\dp_logo.jpg', single_blob) as lob; insert into mig_db.dbo.docs select N'excel1.xlsx','.xlsx',bulkcolumn from openrowset (bulk N'c:\SQL2019\excel1.xlsx', single_blob) as lob; insert into mig_db.dbo.docs select N'pdfdoc.pdf','.pdf',bulkcolumn from openrowset (bulk N'c:\SQL2019\pdfdoc.pdf', single_blob) as lob;
After we finish loading, if we select the table we will see these files in binary format:
select * from mig_db.dbo.docs;
How do we extract the files from the table back to a folder
To export the files we have two ways, we will see first the difficult one and then the easy one.
Hard-way Export (BCP)
The hard way is by using it BCP and his xp_cmdshell. The difficulty is not in using them but in the fact that the files exported by BCP will appear as corrupted as they will need formatting.
The process is as follows:
- Enable xp_cmdshell if it isn't already
- With the next query, let's create the template with the format that the files should have (template.fmt).
- In the file that will be created (template.fmt) let's change where it has 8 with 0.
- Let's run the next BCP query in which we have defined which file we want from the table, to which file path we want it to be exported and the file with the format we created in the previous step.
--hard way exec sp_configure 'show advanced options', '1' RECONFIGURE exec sp_configure 'xp_cmdshell', '1' RECONFIGURE SET NOCOUNT ON; CREATE TABLE tt(the_file VARBINARY(MAX) NOT NULL); GO SET NOCOUNT ON; DECLARE @bcp_cmd VARCHAR(8000); SET @bcp_cmd='BCP '+QUOTENAME(DB_NAME())+'.dbo.tt format nul -T -N -f "C:\SQL2019\export\template.fmt"'; EXEC master.sys.xp_cmdshell @bcp_cmd; DROP TABLE tt; GO -- Anoigoume to file (template.fmt) pou exei dimiourgei kai allazoume tin timi 8 me 0. SET NOCOUNT ON; DECLARE @bcp_cmd VARCHAR(8000); SET @bcp_cmd='BCP "SELECT document FROM mig_db.dbo.docs where docname =''dp_logo.jpg''" QUERYOUT "C:\SQL2019\export\dp_logo.jpg" -T -fC "C:\SQL2019\export\template.fmt"'; EXEC master..xp_cmdshell @bcp_cmd; GO
Easy-way (Ole Automation Procedures)
I didn't make this script myself, the main part of it came from him Jitendra to which I made a few modifications.
The process with this method is very simple. All we have to do is:
- Let's enable 'Ole Automation Procedures' if it isn't already.
- Set the @outputpath variable to the folder where the export will take place.
- Define the query with the data we want to export (the field with the file name and the field with the varbinary) in the table-variable @Doctable.
- Finally, let's run the query all together.
--easy way sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Ole Automation Procedures', 1; GO RECONFIGURE; GO DECLARE @outPutPath varchar(50) = 'C:\SQL2019\export' ------------ <-------- Export path , @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), [FileName] varchar(100), [Doc_Content] varBinary(max) ) INSERT INTO @Doctable([FileName],[Doc_Content]) select docname,document from dbo.docs; ------------ <---------- Export query --SELECT * FROM @table SELECT @i = COUNT(1) FROM @Doctable WHILE @i >= 1 BEGIN SELECT @data = [Doc_Content], @fPath = @outPutPath + '\' +[FileName] FROM @Doctable WHERE id = @i EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instance 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 SET @i -= 1 END
After it is finished we will see that it has exported to the folder all the files we had in the table:
Wouldn't it be better to save the path of the file than the file?