クエリから 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 使った方がよいと思いますが…。