Edgar Edgar - 10 months ago 113
MySQL Question

Openrowset bulk insert on a specific row

I am new to SQL, but am trying to learn its logic, I am assuming bulk insert will insert on all rows in this case a blob. (pdf file) below is my code but what I am trying to accomplish is, inserting a pdf file that I have put on SQL server in a row that has a matching Primary Key that I specify. So far I am missing the where clause to specify the PK

Declare @sql varchar(max)
Declare @filePath varchar(max)
Set @filePath = 'C:\iphone.pdf'
Set @sql='INSERT INTO HDData.dbo.PurchasedCellPhoneInfo(Receipt) SELECT * FROM OPENROWSET(BULK '''+ @filePath+''', SINGLE_BLOB) AS BLOB'

May I use an update t-SQL query instead of insert? and how would I drop the where to specify a specific row I want to insert this blob in?
Any help would be appreciated.

I also have tried this, following @misterPositive's suggestion for update query:

Declare @criteria varchar(50)
SET @criteria ='352014075399147'
UPDATE HDData.dbo.PurchasedCellPhoneInfo SET Receipt =
(SELECT Receipt FROM OPENROWSET (BULK 'C:\352014075399147.pdf, SINGLE_BLOB') a)
WHERE(IMEI = @criteria)

i do recieve this message:
Either a format file or one of the three options SINGLE_BLOB, SINGLE_CLOB, or SINGLE_NCLOB must be specified. i like this update query as it seems to fit what im trying to do.

Answer Source

You can do this to UPDATE:

SET blobField = 
   (SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a) 
WHERE (CriteriaField = @criteria)

Here is another way for PK

CREATE VIEW [dbo].[VWWorkDataLoad]
SELECT RecordLine
FROM [dbo].[WorkDataLoad];

Now BULK INSERT should then look like:

BULK INSERT [dbo].[VWWorkDataLoad] FROM 'D:\NPfiles\TS082114.trn'