Update varbinary column with latest file using filepath column from record

Question

I am trying to update a series of Document_Files that are stored in the following table.

Document_File:

• FileID int
• TypeID int
• FilePath nvarchar(255)
• FileData varbinary(max)

I am trying to update the FileData column for each record of a specific type using the FilePath to locate the BLOB to be imported.

I can update them one at a time using the following:

UPDATE Document_File
SET FileData = (SELECT * FROM OPENROWSET(
    BULK 'C:\Reports\Report - District1.xlsx', SINGLE_BLOB) AS T)
    WHERE FileID = 123456

I've attempted to loop through the series of records of the desired file type using the following:

DECLARE @MyFile varchar(100)
DECLARE @LoopCounter int
DECLARE @FileID varchar(255)
DECLARE @isExists int

SET @LoopCounter = (SELECT COUNT(FilePath) FROM Document_File WHERE TypeID = 123)
SET @FileID = (SELECT TOP 1 FileID FROM Document_File WHERE TypeID = 123) -1

WHILE @LoopCounter > 0
BEGIN
    SET @MyFile = (SELECT TOP 1 FilePath FROM Document_File WHERE TypeID = 123 
        AND FileID > @FileID)
    EXEC master.dbo.xp_fileexist @MyFile, @isExists OUTPUT

    IF(@isExists = 1)
        BEGIN TRY
            BEGIN TRAN
                UPDATE Document_File
                SET FileData = (SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB) AS T)
                WHERE TypeID = 123 AND FileID > @FileID
        END TRY
        BEGIN CATCH
            IF @@TRANCOUNT > 0 BEGIN
            ROLLBACK TRAN
            END
        END CATCH

        IF @@TRANCOUNT > 0  BEGIN
        COMMIT TRAN
    END 

SET @LoopCounter = @LoopCounter -1
SET @FileID = @FileID +1

END

I am getting an error stating:

Cannot bulk load. The file "@MyFile" does not exist

Does anyone know what I'm doing wrong and if there is a better way to accomplish this?

Answer 1

OPENROWSET doesn't accept parameters so your line:

(SELECT * FROM OPENROWSET(BULK N'@MyFile', SINGLE_BLOB)

fails, both because you've quoted the parameter name, and because OPENROWSET doesn't allow parameters.

For this approach to work you have to use dynamic SQL, i.e. dynamically build the SQL string and then execute it.

Here is an example.