0

I have a table in Microsoft SQL Server with file path, now I want to store the files as filestream data in the database.

I want to run a bulk update query to convert each row of the table to the corresponding filestream data using the filepath column.

Columns of my table are SrNo, FilePathLocation, FileStreamBlob, Key.

I am able to run the following query successfully for 1 row but I have over 500+ rows in the table with filepath can anyone please help me with the update query for updating all the rows of the table using the filepath column.

DECLARE @file_stream VARBINARY(MAX)
DECLARE @command nvarchar(1000)
DECLARE @filePath NVARCHAR(128)

SET @filePath = (SELECT FilePathLocation FROM ScanFileTable WHERE SrNo = 2077)

SET @command = N'SELECT @file_stream1 = CAST(bulkcolumn AS varbinary(MAX))
                 FROM OPENROWSET(BULK ''' + @filePath + ''', SINGLE_BLOB) ROW_SET'

EXEC sp_executesql @command, N'@file_stream1 VARBINARY(MAX) OUTPUT',@file_stream1 =@file_stream OUTPUT

UPDATE ScanFileTable 
SET FileStreamBlob = @file_stream 
WHERE SrNo = 2077
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sam
  • 1
  • Just to be clear: the data is not currently stored in the database at all, rather just a filename is stored? And are you generally storing data more than 1Mb in size? Which column of your table is the `ROWGUIDCOL`? – Charlieface Jan 08 '22 at 18:43
  • Which client language are you using? C#, Python, C++, something else? – Charlieface Jan 08 '22 at 18:49
  • Yes, now the data is not stored in the database, just the file path is available. Yes, sometimes the pdf file size might be more than 1Mb(not sure, to be honest). I am storing ROWGUIDCOL in [Key] filed. – Sam Jan 08 '22 at 20:13
  • Actully, my problem is in the above code I am using a variable @filePath instead of that is it possible to pass the column field directly FilePathLocation. BULK ''' + FilePathLocation + ''', SINGLE_BLOB) like this. – Sam Jan 08 '22 at 20:17
  • You can't use `OPENROWSET` with a parameter, so you can't do for example `FROM ScanFileTable CROSS APPLY OPENROWSET (BULK FilePathLocation` I would think this might be far faster and easier with a client app that can loop through the rows and use the Streaming API to upload the file. If the files are not likely to be more than 1Mb or if you don't intend to use the Streaming API then it seems pointless to use `FILESTREAM` you may as well use a normal `varbinary(max)` column. What do you hope to gain by using it? – Charlieface Jan 08 '22 at 20:35
  • As you suggested I wrote a client application in Java to achieve the bulk conversion of files to FILESTREAM by fetching them from the file path. And, it worked as expected. Thank you so much for your valuable suggestion. – Sam Jan 09 '22 at 19:13

0 Answers0