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

Question

I'm trying to put the contents of a .pdf file into a column of type varbinary(max).

How I can achieve this? I played with convert and cast, but it doesn't seem to work.

I was thinking about defining var1 varbinary(max) and setting it to the file's contents, but this also failed.

declare @var1 varbinary(max)
set @var1 'c:\xxx\inp.pdf' ???

and then:

insert into t1(xdata) values ( @var1);

Or maybe I can use insert from file, not sure is this possible without BULK?

Appreciate your help, I can't use contents of .pdf file in single qoutes either, it treats it as varchar -(

Thanks all for help. Dai

Answer 1

Try this:

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

or directly:

INSERT INTO dbo.t1(xdata) 
   SELECT BulkColumn
   FROM OPENROWSET(BULK N'C:\Users\......\YourFile.pdf', SINGLE_BLOB)

More details on the OPENROWSET function can be found on MSDN's SQL Server Books Online as always.