SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

クエリからのバイナリデータのインポート/エクスポート

leave a comment

クエリから SQL Server のバイナリ (varbinary(max)) に対してデータをインポート / エクスポートする際のメモを。

Using OPENROWSET to read large files into SQL Server
Exporting binary file data (images) from SQL via a stored procedure
Exporting Binary Files To The File System

を参考にするとよいかと。

SSIS で実行する場合は以下が参考になるかと。
Import multiple images to SQL Server using SSIS
Export images from a SQL Server Table to a Folder with SSIS
Store Images,Documents,PDF (BLOB) Files Into SQL Server Using SSIS
Retrive Image,Document,PDF (BLOB) Files Stored In SQL Server Tables Using SSIS

インポートについては OPENROWSET を使用することで実行できます。
サンプルとしては以下のようになります。

SET NOCOUNT ON
IF (Object_ID(N'BlobTable') IS NULL)
	CREATE TABLE BlobTable (Filename nvarchar(255), Data varbinary(max))
Truncate Table BlobTable
GO

DECLARE @blob varbinary(max)

SELECT @blob = (SELECT * FROM OPENROWSET(BULK N'C:Import\01.pptx', SINGLE_BLOB) as tmp)
INSERT INTO BlobTable VALUES('01.pptx', @blob)

SELECT @blob = (SELECT * FROM OPENROWSET(BULK N'C:Import\02.pptx', SINGLE_BLOB) as tmp)
INSERT INTO BlobTable VALUES('02.pptx', @blob)

SELECT @blob = (SELECT * FROM OPENROWSET(BULK N'C:Import\03.pptx', SINGLE_BLOB) as tmp)
INSERT INTO BlobTable VALUES('03.pptx', @blob)

SELECT @blob = (SELECT * FROM OPENROWSET(BULK N'C:Import\04.pdf', SINGLE_BLOB) as tmp)
INSERT INTO BlobTable VALUES('04.pdf', @blob)

 

エクスポートについては BCP を QueryOut で使用することで実行できます。

クエリから BCP を実行するために xp_cmdshell を一時的に有効にする必要がありますが…。

EXEC sp_configure 'show advanced options', 1
RECONFIGURE WITH  OVERRIDE

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE WITH  OVERRIDE
GO

DECLARE @FileName varchar(255), @Blob varbinary(max)
DECLARE @sql varchar(4000), @result int
DECLARE Blob_Cursor CURSOR FOR 
SELECT * FROM BlobTable

OPEN Blob_Cursor

FETCH NEXT FROM Blob_CUrsor
INTO @FileName, @Blob
WHILE @@FETCH_STATUS = 0
BEGIN
	SET @sql = 'bcp "SELECT data FROM BCPDB..blobTable WHERE FileName = ''' + @FileName + '''" queryout C:Export' + @FileName + ' -T -S localhost -f C:Exportbinary.fmt'
	PRINT @sql
	EXEC xp_cmdshell @sql
	FETCH NEXT FROM Blob_CUrsor
	INTO @FileName, @Blob
END

CLOSE Blob_Cursor
DEALLOCATE Blob_Cursor


BCP で出力をする際には以下のようなフォーマットファイルを使用する必要があります。

11.0
1
1       SQLBINARY           0       0       ""   1     data         ""

これでクエリから BCP を使用してバイナリのデータをエクスポートすることができます。

この方法、SQL Server 内にエクスポートするので、SSIS 使った方がよいと思いますが…。

Written by masayuki.ozawa

10月 28th, 2014 at 10:45 pm

Posted in SQL Server

Tagged with

Leave a Reply

*