SqlServer 如何插入图片和导出图片数据

SqlServer 2005开始可以通过OPENROWSET和bcp操作，下面是两种实现方法。

一、 示例1

• SQL Server import p_w_picpath - SQL Server export p_w_picpath
• Extract p_w_picpath SQL Server - SQL Server export binary data

1. 创建目标表

USE AdventureWorks;

-- Create p_w_picpath warehouse for importing p_w_picpath into sql database
CREATE TABLE dbo.PhotoLibrary (
  PhotoLibraryID INT IDENTITY ( 1 , 1 ) PRIMARY KEY,
  ImageName        VARCHAR(100),
  Photo            VARBINARY(MAX));

2. 插入图片数据

INSERT INTO dbo.PhotoLibrary ([ImageName]) VALUES ('MadisonAVE.JPG');

UPDATE dbo.PhotoLibrary
SET    Photo = (SELECT *
FROM OPENROWSET(BULK 'e:\p_w_picpath\photo\MadisonAVE.JPG',
SINGLE_BLOB) AS x)
WHERE  [ImageName] = 'MadisonAVE.JPG';

-- Check table population
SELECT * FROM   dbo.PhotoLibrary;

3. 导出图片数据

-- SQL Server export p_w_picpath
DECLARE  @Command NVARCHAR(4000)
-- Keep the command on ONE LINE - SINGLE LINE!!!
SET @Command = 'bcp "SELECT Photo FROM AdventureWorks.dbo.PhotoLibrary" queryout "e:\p_w_picpath\photo\expMadisonAVE.jpg" -T -n -SPROD\SQL2005'
PRINT @Command -- debugging
EXEC xp_cmdshell @Command
GO

 二、 示例2

• T-SQL Export all p_w_picpaths in table to file system folder
• Source table: Production.ProductPhoto
• Destination: K:\data\p_w_picpaths\productphoto\

USE AdventureWorks2008;

DECLARE  @Command       VARCHAR(4000),
         @PhotoID       INT,
         @ImageFileName VARCHAR(128)

DECLARE curPhotoImage CURSOR FOR -- Cursor for each p_w_picpath in table
SELECT ProductPhotoID,
       LargePhotoFileName
FROM   Production.ProductPhoto
WHERE  LargePhotoFileName != 'no_p_w_picpath_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!!!
SET @Command = 'bcp "SELECT LargePhoto FROM AdventureWorks2008.Production.ProductPhoto WHERE ProductPhotoID = ' + convert(VARCHAR,@PhotoID) + '" queryout 
 K:\data\p_w_picpaths\productphoto\' + @ImageFileName + '" -T -n -SHPESTAR'
PRINT @Command – debugging 
 
-- bcp "SELECT LargePhoto FROM AdventureWorks2008.Production.ProductPhoto WHERE ProductPhotoID = 69" queryout "K:\data\p_w_picpaths\productphoto\racer02_black_f_large.gif" -T -n -SHPESTAR
 
EXEC xp_cmdshell @Command     -- Carry out p_w_picpath export to file from db table
FETCH NEXT FROM curPhotoImage
INTO @PhotoID,
         @ImageFileName
END -- cursor loop
CLOSE curPhotoImage
DEALLOCATE curPhotoImage