Inserting a pdf file to SQL table using SQL

Question

I am trying to insert a pdf file into a sql table (varbinary column)

create table pdfTest(pdfData varbinary(max))
declare @filePath varchar(100)
set @filePath = 'c:\pdfSample.pdf'
INSERT INTO pdfTest (pdfData) SELECT * FROM OPENROWSET(BULK @filePath, SINGLE_BLOB) AS BLOB

However this gives an error

 Incorrect syntax near '@filePath'.

None of the following assignment works

 set @filePath = 'c:'\pdfSample.pdf'
 set @filePath = 'c:\\pdfSample.pdf'

But the following syntax works

 INSERT INTO pdfTest (pdfData) SELECT * FROM OPENROWSET(BULK 'c:\pdfSample.pdf', SINGLE_BLOB) AS BLOB

Just wondering how @filePath can be used in the insert statement?

Thank you

Answer 1

I think here the variable name is not getting resolved. Try using the variable name in dynamic sql.

 Declare @sql varchar(max)
 Set @sql='INSERT INTO pdfTest (pdfData) SELECT * FROM OPENROWSET(BULK'+ @filePath+', SINGLE_BLOB) AS BLOB'
 exec @sql