Import & export images in SQL

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

.....

*/