Do you need to extract images from a MS SQL database that contains lot of images such as Photos or signature images?
Here is a Query to do this:
For example consider a bank's database that contains customer's signature or photos.
Tables used in the below query:
indexregister - Contains Account IDs with customer details.
image_record_table - Contains Signature / Photo images (scanned images)
/** Chnage Scheme in the below Line OR the select query according to your need**/
DECLARE CURSOR_AcctIds CURSOR FOR (SELECT acctno FROM indexregister where scheme = 'SB')
DECLARE @Code nvarchar(1024);
/** Set Scheme Code **/
SELECT @Code = 'SB';
DECLARE @Path nvarchar(1024);
/** Set Output Folder Path **/
SELECT @Path = 'D:\Images';
DECLARE @AcctId INT;
OPEN CURSOR_AcctIds
FETCH NEXT FROM CURSOR_AcctIds INTO @AcctId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @ImageData varbinary(max);
SELECT @ImageData = (SELECT convert(varbinary(max), pic_name, 1) FROM image_record_table WHERE acctno = @AcctId);
DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT acctno FROM image_record_table where acctno = @acctid);
DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + '\' + @Code + @Filename+ '.bmp';
DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
EXEC sp_OASetProperty @ObjectToken, 'Type', 1;
EXEC sp_OAMethod @ObjectToken, 'Open';
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, 'Close';
EXEC sp_OADestroy @ObjectToken;
FETCH NEXT FROM CURSOR_AcctIds INTO @AcctId
END
CLOSE CURSOR_AcctIds
DEALLOCATE CURSOR_AcctIds
After executing this query the images are stored in the output folder path that is specified in this query. The filenames will be Code+AcctId.
This query supports any type of file (jpg, png, bmp) stored as Binary data.
Customize and use the above query for your need.
Category