datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How import and export image column data?

Several methods are available for importing & exporting images / photos / videos / documents / BLOBs.

SQL Server 2000: the following undocumented utility in the binn folder can be used. It prompts you for the parameteres. In command prompt execute the following command:

    textcopy

SQL Server 2005 and on OPENROWSET and bcp:

-- SQL Server import image - SQL Server export image
-- Extract image SQL Server - SQL Server export binary data

USE AdventureWorks;

GO 

-- Create image warehouse for importing image into sql database

CREATE TABLE dbo.PhotoLibrary (

  PhotoLibraryID 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.PhotoLibrary

           ([ImageName])

VALUES     ('MadisonAVE.JPG')

 

UPDATE dbo.PhotoLibrary

SET    Photo = (SELECT *

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

                       SINGLE_BLOB) AS x)

WHERE  [ImageName] = 'MadisonAVE.JPG'

GO

 

-- Check table population

SELECT *

FROM   dbo.PhotoLibrary

GO

 

-- SQL Server export image

DECLARE  @Command NVARCHAR(4000)

 

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

SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary"

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

 

PRINT @Command -- debugging

 

EXEC xp_cmdshell   @Command

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(4000),

         @PhotoID       INT,

         @ImageFileName VARCHAR(128)

 

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

SELECT ProductPhotoID,

       LargePhotoFileName

FROM   Production.ProductPhoto

WHERE  LargePhotoFileName != 'no_image_available_large.gif'

 

OPEN curPhotoImage

 

FETCH NEXT FROM curPhotoImage

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 -SHPESTAR'

    

    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 -SHPESTAR

*/

    

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

    

    FETCH NEXT FROM curPhotoImage

    INTO @PhotoID,

         @ImageFileName

  END  -- cursor loop

 

CLOSE curPhotoImage

 

DEALLOCATE curPhotoImage

/*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

.....

*/

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

SSIS Import / Export Solutions for image transfer:

      Export Column & Import Column Transformations

Related articles:

Stored procedure to export/import images to SQL Server

Export image datatype to disk

FILESTREAM MVC: Download and Upload images from SQL Server

 

Exam Prep 70-461
Exam 70-461