SQL Server Binary File

Question

how do I download images, content, files that are stored directly in the database in binary form? I ask this, because I am in a data migration and the layout (spreadsheet in excel) of the new system asks the file path, so the idea would be to download these files and play to a specific folder.

Below the select of the table with the information, as well as an example of data output.

Consultation

SELECT [id]
    ,[file_name]
    ,[file_content]
FROM service_req_files
WHERE ID = 1073

Output Example

ID      file_name       file_content
1073    image001.png    0xFFD8FFE000104A4649460001010100...

Answer 1

You can use a T-SQL script like the one in the example:

USE AdventureWorks;



-- Create image warehouse for importing image into sql database

CREATE TABLE dbo.ImageWarehouse (

  ImageWarehouseID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  ImageName        VARCHAR(100),

  Photo            VARBINARY(MAX))

GO



-- SQL Server import image - sql storing images database sql server

INSERT INTO dbo.ImageWarehouse

           ([ImageName])

VALUES     ('5THAVE.JPG')



UPDATE dbo.ImageWarehouse

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'e:\image\photo\5THAVE.JPG',

                       SINGLE_BLOB) AS x)

WHERE  [ImageName] = '5THAVE.JPG'

GO



-- Check table population

SELECT *

FROM   dbo.ImageWarehouse

GO



-- SQL Server export image

DECLARE  @SQLcommand NVARCHAR(4000)



-- Keep the command on ONE LINE - SINGLE LINE!!! - broken here for presentation

SET @SQLcommand = 'bcp "SELECT Photo FROM AdventureWorks.dbo.ImageWarehouse"

                   queryout "e:\image\photo\exp5THAVE.jpg" -T -n -SPROD\SQL2005'



PRINT @SQLcommand -- debugging



EXEC xp_cmdshell   @SQLcommand

GO

------------

-- T-SQL Export all images in table to file system folder

-- Source table: Production.ProductPhoto  - Destination: K:\data\images\productphoto\

------------



USE AdventureWorks2008;

GO



DECLARE  @Command       VARCHAR(4096),   -- dynamic command

         @PhotoID       INT,

         @ImageFileName VARCHAR(128)



DECLARE crsImage CURSOR  FOR             -- Cursor for each image in table

SELECT ProductPhotoID,

       LargePhotoFileName

FROM   Production.ProductPhoto

WHERE  LargePhotoFileName != 'no_image_available_large.gif'



OPEN crsImage



FETCH NEXT FROM crsImage

INTO @PhotoID,

     @ImageFileName



WHILE (@@FETCH_STATUS = 0) -- Cursor loop 

  BEGIN

-- Keep the bcp command on ONE LINE - SINGLE LINE!!! - broken up for presentation

    SET @Command = 'bcp "SELECT LargePhoto FROM

    AdventureWorks2008.Production.ProductPhoto WHERE ProductPhotoID = ' +

    convert(VARCHAR,@PhotoID) + '" queryout "K:\data\images\productphoto\' +

    @ImageFileName + '" -T -n -SYOURSERVER'



    PRINT @Command -- debugging 

/* bcp "SELECT LargePhoto FROM AdventureWorks2008.Production.ProductPhoto

WHERE ProductPhotoID = 69" queryout

"K:\data\images\productphoto\racer02_black_f_large.gif" -T -n -SYOURSERVER

*/



    EXEC xp_cmdshell @Command     -- Carry out image export to file from db table



    FETCH NEXT FROM crsImage

    INTO @PhotoID,

         @ImageFileName

  END  -- cursor loop



CLOSE crsImage

DEALLOCATE crsImage

/*output

NULL

Starting copy...

NULL

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 16     Average : (62.50 rows per sec.)

NULL

.....

*/

------------

You can have more references in:

At this link

Example given above

This too, but more complex