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