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

Sonam · Accepted Answer · 2013-08-23 20:21:16Z

4

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

answered Aug 23, 2013 at 20:21

Sonam

3,4861 gold badge14 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

blue piranha Over a year ago

This gives error: Could not find stored procedure 'INSERT INTO pdfTest (pdfData) SELECT * FROM OPENROWSET(BULKc:\pdfSample.pdf, SINGLE_BLOB) AS VARBINARY'. it's removing the single quotes from the filePath

i-one Over a year ago

@bluepiranha, just change exec @sql to exec(@sql)

i-one Over a year ago

@bluepiranha, and OPENROWSET(BULK'+ @filePath+', SINGLE_BLOB) to OPENROWSET(BULK'''+ @filePath+''', SINGLE_BLOB)

Reza · Accepted Answer · 2015-03-25 15:23:53Z

-1

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

edited Mar 25, 2015 at 15:23

Reza

20.1k18 gold badges102 silver badges178 bronze badges

answered Mar 25, 2015 at 14:16

Sudhakar

1

Collectives™ on Stack Overflow

Inserting a pdf file to SQL table using SQL

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related