T-SQL writing a blob to an external file
Posted on 2009-07-07
Having just figured out how to use OPENROWSET(BULK '...', SINGLE_BLOB) to read binary data from the file system into my SQL Server database, I now need to do the opposite:
Given an "image" type column in a table, I need T-SQL script to write this to a file somewhere on the file system. Again, ideally as NATIVE as possible.
(dang - why are examples of this so hard to find?)
So imagine your select statement to get the blob is
SELECT [data] from [dbo].[blobtable] where [id] = 5
the [data] happens to represent a JPG, so what I need is a wee bit o' T-SQL code that will write [data] out of the above select to 'C:\test.jpg'