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

Hehuyi_In

已于 2023-01-20 22:12:22 修改

阅读量5.5k

点赞数

分类专栏：脚本&命令 SQLServer 文章标签： sqlserver 数据库 sql

于 2019-09-01 23:02:22 首次发布

原文链接：https://blog.51cto.com/ultrasql/1585003

版权

SQLServer 同时被 2 个专栏收录

142 篇文章 26 订阅

订阅专栏

脚本&命令

61 篇文章 8 订阅

订阅专栏

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