I have a table called ImageValues. In it, I have columns labeled RecordID, ReportID and Image. The image is stored as an IMAGE data type. I want to extract the image to a file and write the file path into another column labeled FilePath. Preferably, the filename would be "RecordID-ReportID"
I am using SQL Server
Can this be done?
One of the ways to do it is using OLE automation, which you can enable if you have administrator privileges on the machine. Here's some sample code, liberally borrowed from this SO answer:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
DECLARE @file int,
@FilePath varchar(80),
@hr INT,
@RecordID INT,
@ReportID INT,
@Image VARBINARY(MAX);
DECLARE imgs CURSOR
FOR SELECT RecordID, ReportID, "Image" FROM dbo.ImageValues
FOR UPDATE OF FilePath;
OPEN imgs;
FETCH NEXT FROM imgs INTO @RecordID, @ReportID, @Image;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @FilePath = 'E:/' + CAST(@RecordID AS VARCHAR(10)) + '-' + CAST(@ReportID AS VARCHAR(10)) + '.jpg';
EXEC sp_OACreate 'ADODB.Stream', @file OUT;
EXEC sp_OASetProperty @file, 'Type', 1;
EXEC sp_OAMethod @file, 'Open';
EXEC sp_OAMethod @file, 'Write', NULL, @Image;
EXEC sp_OAMethod @file, 'SaveToFile', NULL, @FilePath, 2;
EXEC sp_OAMethod @file, 'Close';
EXEC sp_OADestroy @file;
UPDATE dbo.ImageValues SET FilePath = @FilePath WHERE CURRENT OF imgs;
FETCH NEXT FROM imgs INTO @RecordID, @ReportID, @Image;
END
CLOSE imgs;
DEALLOCATE imgs;
GO
sp_configure 'Ole Automation Procedures', 0;
GO
RECONFIGURE;
GO
Note that all files are going to be stored with the extension '.jpg', but you could extend it to detect the image type based on the magic number.
If you're using SQL Server 2012 or later, you could also use a cool feature called FileTables. Read up on them here, and also here for ways of inserting data into a FileTable using T-SQL. After that, you can simply copy the files wherever you want using Windows Explorer.
