在SQL Server中使用纯SQL在数据库之间复制BLOB值

I need to pull some BLOB data from a SQL Server 2005 database and generate a SQL script to insert this same data in another database, in another server.

我需要从SQL Server 2005数据库中提取一些BLOB数据并生成一个SQL脚本,以将该相同数据插入另一个服务器中的另一个数据库中。

I am only allowed to do this using SQL scripts, I can't use any other utility or write a program in Java or .NET to do it.

我只允许使用SQL脚本执行此操作,我不能使用任何其他实用程序或使用Java或.NET编写程序来执行此操作。

The other big restriction I have is that I don't have access to the original database (where the original BLOB data is) when I run the script, to copy the BLOB data to the target database, so the data should already be encoded within the SQL script file.

我的另一个重要限制是,当我运行脚本时,我无法访问原始数据库(原始BLOB数据所在的位置),将BLOB数据复制到目标数据库,因此数据应该已经在SQL脚本文件。

Summing up: is there a way to encode the BLOB data into text so that I can dump it into a SQL INSERT command within a script text file and run it?

总结:有没有办法将BLOB数据编码为文本,以便我可以将其转储到脚本文本文件中的SQL INSERT命令中并运行它?

I am able to run special T-SQL statements and stored procedures if needed to.

如果需要,我可以运行特殊的T-SQL语句和存储过程。

3 个解决方案

#1

TEXTCOPY was a sample application included in SQL Server 7.0 and 2000 but no longer available in SQL Server 2005.

TEXTCOPY是SQL Server 7.0和2000中包含的示例应用程序,但在SQL Server 2005中不再可用。

However, googling for TEXTCOPY in SQL Server 2005, I found this alternative that might do the trick:

但是,在SQL Server 2005中使用Google搜索TEXTCOPY,我发现这个替代方案可以解决这个问题:

http://sequelserver.blogspot.com/2007/01/texcopy-sql-server-2005.html

It relies on writing and reading the binary data from the filesystem, which in my case is not ideal (ideally I would like to encode the binary data with the SQL script text file itself), but it's the best I've found so far.

它依赖于从文件系统中编写和读取二进制数据,在我看来这是不理想的(理想情况下我想用SQL脚本文本文件本身编码二进制数据),但它是迄今为止我发现的最好的。

Here's another good source on how to do binary import/export operations using BULK OPENROWSET: http://msdn.microsoft.com/en-us/library/ms191184.aspx

以下是使用BULK OPENROWSET进行二进制导入/导出操作的另一个好消息来源:http://msdn.microsoft.com/en-us/library/ms191184.aspx

#2

This article "Copy Text or Image into or out of SQL Server" could help:

本文“将文本或图像复制到SQL Server中或从中复制出来”可能会有所帮助:

You could integrate the TEXTCOPY command line tool in a stored procedure:

您可以将TEXTCOPY命令行工具集成到存储过程中:

CREATE PROCEDURE sp_textcopy (
  @srvname     varchar (30),
  @login       varchar (30),
  @password    varchar (30),
  @dbname      varchar (30),
  @tbname      varchar (30),
  @colname     varchar (30),
  @filename    varchar (30),
  @whereclause varchar (40),
  @direction   char(1))

AS

DECLARE @exec_str varchar (255)
SELECT @exec_str =
         'textcopy /S ' + @srvname +
         ' /U ' + @login +
         ' /P ' + @password +
         ' /D ' + @dbname +
         ' /T ' + @tbname +
         ' /C ' + @colname +
         ' /W "' + @whereclause +
         '" /F ' + @filename +
         ' /' + @direction
EXEC master..xp_cmdshell @exec_str

You'll have to change/extend it a little in order to read the created file into the other database.

您必须稍微更改/扩展它才能将创建的文件读入另一个数据库。

As Vinko writes in the comment to this answer, hold in mind that this requires enabling xp_cmdshell in the surface area configuration.

正如Vinko在评论中写到的那样,请记住,这需要在表面区域配置中启用xp_cmdshell。

Description of TEXTCOPY:

TEXTCOPY的描述:

Copies a single text or image value into or out of SQL Server. The value is a specified text or image 'column' of a single row (specified by the "where clause") of the specified 'table'.

将单个文本或图像值复制到SQL Server或从SQL Server复制出来。该值是指定“table”的单个行(由“where子句”指定)的指定文本或图像“列”。

If the direction is IN (/I) then the data from the specified 'file' is copied into SQL Server, replacing the existing text or image value. If the direction is OUT (/O) then the text or image value is copied from SQL Server into the specified 'file', replacing any existing file.

如果方向为IN(/ I),则指定“文件”中的数据将复制到SQL Server中,替换现有的文本或图像值。如果方向为OUT(/ O),则将文本或图像值从SQL Server复制到指定的“文件”中,替换任何现有文件。

#3

Take a look at this question and search for bcp on the page - has example of both import and export for varbinary(max) (which is the new standard type for such columns). you can run arbitrary query for export.

看一下这个问题并在页面上搜索bcp - 有varbinary(max)导入和导出的例子(这是这些列的新标准类型)。你可以运行任意查询导出。

How to insert a blob into a database using sql server management studio

如何使用sql server management studio将blob插入数据库

#1