{"id":3447,"date":"2021-07-12T08:00:00","date_gmt":"2021-07-12T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3447"},"modified":"2023-05-01T02:26:27","modified_gmt":"2023-04-30T23:26:27","slug":"pos-mporoyme-na-apothikeyoyme-mesa-se-p","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-mporoyme-na-apothikeyoyme-mesa-se-p\/","title":{"rendered":"How can we store docx, xlsx, jpg, xml, etc. files in a database table? in SQL Server"},"content":{"rendered":"

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. <\/p>\n\n\n\n

How do we import the files from a folder into the table<\/h2>\n\n\n\n

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)<\/strong> which will contain the entire file in binary <\/strong>form.<\/p>\n\n\n\n

create table mig_db.dbo.docs (\nid int identity(1,1),\ndocname nvarchar(200),\nfiletype varchar(100),\ndocument varbinary(max)\n);\n\nalter table mig_db.dbo.docs add constraint pk_docs primary key clustered(id);<\/pre>\n\n\n\n
The files we want to upload are located in the following folder on our server:<\/p>\n\n\n\n
\"\"
1<\/figcaption><\/figure>\n\n\n\n
All we have to do now is load in the array with insert <\/strong>one by one the files we want. The field that will contain the file is loaded as blob <\/strong>(binary large object) directly with the known function openrowset<\/a>, as shown in the following examples:<\/p>\n\n\n\n
insert into mig_db.dbo.docs\nselect N'doc1.docx','.docx',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\doc1.docx', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'doc2.docx','.docx',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\doc2.docx', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'doc3.doc','.doc',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\doc3.doc', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'dp_logo.jpg','.jpg',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\dp_logo.jpg', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'excel1.xlsx','.xlsx',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\excel1.xlsx', single_blob) as lob;\n\ninsert into mig_db.dbo.docs\nselect N'pdfdoc.pdf','.pdf',bulkcolumn from openrowset (bulk N'c:\\SQL2019\\pdfdoc.pdf', single_blob) as lob;\n<\/pre>\n\n\n\n
After we finish loading, if we select the table we will see these files in binary format:<\/p>\n\n\n\n
select * from mig_db.dbo.docs;<\/pre>\n\n\n\n
\"\"
2<\/figcaption><\/figure>\n\n\n\n
How do we extract the files from the table back to a folder<\/h2>\n\n\n\n
To export the files we have two ways, we will see first the difficult one and then the easy one.<\/p>\n\n\n\n
Hard-way Export (BCP)<\/h4>\n\n\n\n
The hard way is by using it BCP<\/a> and his xp_cmdshell<\/a>. 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.<\/p>\n\n\n\n
The process is as follows:<\/p>\n\n\n\n