SQL Server 2008 R2:将BLOB和其他列数据导出到文件系统的最佳方法 [英] SQL Server 2008 R2: Best way to export BLOB and other column data out to the file system

问题描述

我已经阅读了一些有关BCP或CLR代码的技术，这些技术会将BLOB导出到硬盘驱动器上的单个文件中，但是我需要将BCP或CLR一起输出整个表数据(这意味着其他列是字符，整数或日期时间)数据需要整体导出)，我需要确保可以将BCP/CLR数据返回到表中，并且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.

有什么想法吗?

推荐答案

我不确定我是否理解您的要求，所以我将尝试介绍两种情况.

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

首先，如果要将所有数据(包括varbinary blob)导出到一个文件中，则可以执行此操作.这是与表一起使用的测试脚本.您必须在SSMS中打开SQLCMD模式.然后发出以下脚本:

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:

-- 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

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

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

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

如果适用，您可以共享导出blob的文件夹，并从客户端PC对其进行访问.

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

这篇关于SQL Server 2008 R2:将BLOB和其他列数据导出到文件系统的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！