How to Insert all PDF files from a folder into a varbinary(max) column in SQL using a Query

Question

I am trying to create a query script for SQL Server that will convert pdf file into varbinary data and store it into the SQL Table column which is varbinary(max). I also have to make sure it loops so it can do that for the next pdf file in that folder. This folder will only contain pdf files.

DECLARE @pdf VARBINARY(MAX)

SELECT @pdf = BulkColumn
FROM OPENROWSET(BULK N'C:\Users\......\YourFile.pdf', SINGLE_BLOB) AS Document;

SELECT @pdf, DATALENGTH(@pdf)

INSERT INTO dbo.YourTable(PDFContents) VALUES(@Pdf)
GO

How to put .pdf file contents into varbinary(max) colunm

Answer 1

So it sounds like you do not have to parse the pdf, just import the entire thing into one column. If so, you could try using SSIS. Create a data flow task and use the Import Column transformation, this should allow you to import the pdf as an image; you'd just need to specify the path to your files. As far as processing several files in the folder, create a ForEach Loop for the data flow task. And for the automation part, just schedule the ssis package via sql jobs to run at specific times or on a regular schedule.

Answer 2

Assume you have SQLPS installed (in sql server 2012+, it is called SQLPS MODULE but in SQL Server 2008 R2, it is called SQLPS SnapIns)

You can run the following one-line PS to solve your problem. (I run it in my local computer, which has a DEFAULT sql server instance)

Here is the table I created for holding my PDF files

use tempdb 
create table dbo.tblPDF (id int identity, pdf varchar(max));

Here is PS line

dir c:\test\*.pdf | % { [string]$query = "insert into tempdb.dbo.tblPDF (pdf) select x from openrowset (bulk N'$($_)', SINGLE_BLOB) as T(x)"; INVOKE-SQLCMD -SERVER . -Database tempdb -Query $query -QueryTimeout 3600};