DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to import & export image using varbinary(max)?

Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to import and export a jpeg photo image:

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

.....

*/

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

Related article:

http://www.sqlusa.com/bestpractices/imageimportexport/

 

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE