SQL Server 2008 R2:将BLOB和其他列数据导出到文件系统的最佳方法。

I have read some arts about BCP or CLR code that will export BLOBs to individual files on the hard drive, but I need to BCP or CLR out the entire table data together (meaning the other columns which are character or integer or datetime data need to come out as a whole) I need to make sure I can BCP/CLR the data back into the table and have the same linking between BLOBs and other column data.

我读过一些关于BCP的艺术或CLR代码,将出口blob单个文件在硬盘上,但我需要BCP或CLR整个表数据(即其他字符或整数或datetime列数据需要出来作为一个整体)我需要确保我能BCP / CLR回的数据表和有相同的斑点和其他列数据之间的链接。

Any ideas?

什么好主意吗?

2 个解决方案

#1

I'm not sure if I understand what you're asking, so I'll try to cover two cases.

我不确定我是否理解你的意思，所以我会试着讲两种情况。

First, if you'd like to export all your data (including varbinary blobs) into one file, you can do it. Here's a test script to use with your table. You have to turn on SQLCMD mode in your SSMS. Then issue this script:

首先，如果您想将所有数据(包括varbinary blobs)导出到一个文件中，您可以这样做。这里有一个与您的表一起使用的测试脚本。在SSMS中必须打开SQLCMD模式。然后问题这个脚本:

-- create target structure same as source
select top 0 *
into YourTbl2
from YourTbl

-- first line makes BCP dump to file, second line imports it to target structure    
!!bcp YourDb.dbo.YourTbl out "D:\Temp\BlobTest.bak" -T -c 
!!bcp YourDb.dbo.YourTbl2 in "D:\Temp\BlobTest.bak" -T -c 

-- test if everything is imported
select * from Playground.dbo.BlobTest
except
select * from Playground.dbo.BlobTest2

If you want to just export an individual file to disk where your SQL Server resides, you can use this:

如果要将单个文件导出到SQL服务器所在的磁盘，可以使用以下方法:

!!bcp "SELECT TOP 1 YourBlob FROM YourDb.dbo.YourTbl" queryout "D:\Temp\YourFile.xyz" -T -c

If it's applicable, you can share the folder where you're exporting your blob and access it from your client PC.

如果适用，您可以共享导出blob的文件夹，并从客户端PC访问它。

#2

You can do like the Following : You can have Image Data Type to Hold Any Files with in. And, Please Read the Following Thoroughly to Understand. I've implemented this in our Project. Simple and Fully Dynamic. You just have to call :

您可以这样做:您可以使用图像数据类型来保存任何文件。请仔细阅读下面的内容，以便理解。我在我们的项目中实现了这个。简单的和完全动态。你只需要打电话:

Insert [tblTemp] (imageType,ImageFile) Select '.PDF',BulkColumn from Openrowset( Bulk     'C:\mak\A.PDF', Single_Blob) as tb

to Insert into the Table and You can Use :

插入到表格中，你可以使用:

 WriteBinaryToDisc 'C:\NEWF\','MAK','.PDF','DOC_TABLE','DOC_ID','DOC_IMAGE','WHERE     DOC_ID=''25'''

to write back to the File System with Your specified Location and Extension.

要用指定的位置和扩展回写文件系统。

I've used an tblTemp to hold all the Files.

我使用了tblTemp来保存所有文件。

--FIRST CHANGE THE CONFIGURATION TO ACTIVATE THIS FEATURE
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

--HOW TO WRITE FILES TO DIRECTLY SQL SERVER FROM DISC
CREATE TABLE [dbo].[tblTemp](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [ImageType] [varchar] (10) NULL,
    [ImageFile] [image] NULL
) ON [PRIMARY]

Insert [tblTemp] (imageType,ImageFile) Select '.PDF',BulkColumn from Openrowset( Bulk     'C:\mak\A.PDF', Single_Blob) as tb
-----------------------------------------------------

--HOW TO WRITE FILE TO DISC FROM SQL SERVER
--WriteBinaryToDisc 'C:\NEWF\','MAK','.PDF','DOC_TABLE','DOC_ID','DOC_IMAGE','WHERE     DOC_ID=''25'''
ALTER PROCEDURE WriteBinaryToDisc 
(
    @Path VARCHAR(255),
    @Filename VARCHAR(100),
    @FileExt VARCHAR(4),
    @TblName varchar(50),
    @IDField VARCHAR(50),
    @ImageField VARCHAR(50),
    @WHERE VARCHAR(300)
)
AS
set nocount on
EXEC ('
DECLARE @SOURCEPATH VARBINARY(MAX),
@DESTPATH VARCHAR(MAX),
@ObjectToken INT,
@image_ID BIGINT

DECLARE IMGPATH CURSOR FAST_FORWARD FOR SELECT '+@ImageField+','+@IDField+' from     '+@TblName+' '+@WHERE+' 
OPEN IMGPATH

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID

WHILE @@FETCH_STATUS = 0
BEGIN
SET @DESTPATH = '''+@Path+'\'+@Filename+'''+ CAST(@image_ID AS     varchar)+'''+@FileExt+'''

EXEC sp_OACreate ''ADODB.Stream'', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, ''Type'', 1
EXEC sp_OAMethod @ObjectToken, ''Open''
EXEC sp_OAMethod @ObjectToken, ''Write'', NULL, @SOURCEPATH
EXEC sp_OAMethod @ObjectToken, ''SaveToFile'', NULL, @DESTPATH, 2
EXEC sp_OAMethod @ObjectToken, ''Close''
EXEC sp_OADestroy @ObjectToken

FETCH NEXT FROM IMGPATH INTO @SOURCEPATH, @image_ID
END

CLOSE IMGPATH
DEALLOCATE IMGPATH
')
---------------------------------------------------------------

#1