Sql-server – How tonsert all PDF files from a folder into a varbinary(max) column in SQL using a Query

sql serversql-server-2008-r2t-sql

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

Best Answer

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.

Related Question