如何为包含VARBINARY(MAX)字段的表生成INSERT脚本？

Question

我有一个包含VARBINARY(MAX)字段的表（在具有FILESTREAM功能的SQL Server 2008中）。

我的要求是，当我准备部署到生产环境时，只能向我的IT团队提供一组按特定顺序执行的SQL脚本。我正在生产环境中创建一个新表，其中包含此VARBINARY(MAX)字段。通常情况下，对于新表，我会编写CREATE TABLE脚本。如果需要携带数据，则接着编写INSERT脚本。这并不太复杂。

但是使用VARBINARY(MAX)字段时，我使用的存储过程在该表上生成INSERT语句时失败了。我尝试选择该字段、打印它、复制它、转换为十六进制等。主要问题在于它没有选定字段中的所有数据。我进行检查DATALENGTH([FileColumn])，如果源行中包含1,004,382个字节，则再次插入时可以获取的最大复制或选定数据为8000。所以基本上是被截断的（即无效的）数据......

我应该如何更好地做到这一点？我尝试疯狂地在Google上搜索，但我肯定漏掉了什么。请记住，我无法访问文件系统。这必须全部用脚本完成。

- Issa Fram

Answer 1

如果这是一次性（或很少）的操作，您可以尝试从SSMS向导中脚本化数据，如此处所述：http://sqlblog.com/blogs/eric_johnson/archive/2010/03/08/script-data-in-sql-server-2008.aspx。

或者，如果您需要经常执行并希望自动化，可以尝试SQL# SQLCLR库（我编写了大部分免费的内容，但您在此处需要的函数不是）。 这个函数是DB_DumpData，它还生成INSERT语句。

但是，如果这是一次性或不频繁的任务，请尝试内置于管理工具中的数据导出向导。 这应该允许您创建可在生产环境中运行的SQL脚本。 我刚刚在一个包含3,365,964字节数据的VARBINARY(MAX)字段的表上进行了测试，生成脚本向导生成了一个完整的6.73百万字符的十六进制字符串值的INSERT语句。

更新：另一种快速简便的方法是将值转换为XML，从而使您可以将整个INSERT语句复制/粘贴到SQL脚本中，无需使用BCP或SSMS导出向导。 首先，您需要使用可选样式“1”将VARBINARY转换为VARCHAR(MAX)，这会给您一个以“0x”开头的十六进制字符串。 一旦您拥有二进制数据的十六进制字符串，就可以将其连接到一个INSERT语句中，并且整个内容在转换为XML时可以包含整个VARBINARY字段。 请参见以下示例：

DECLARE @Binary VARBINARY(MAX) = CONVERT(VARBINARY(MAX),
                                         REPLICATE(
                                           CONVERT(NVARCHAR(MAX), 'test string'),
                                           100000)
                                        )

SELECT 'INSERT INTO dbo.TableName (ColumnName) VALUES ('+
       CONVERT(VARCHAR(MAX), @Binary, 1) + ')' AS [Insert]
FOR XML RAW;

- Solomon Rutzky

Answer 2

不要从SSMS脚本

bcp将数据导出/导入，或使用类似SSMS工具生成INSERT语句。

- gbn

Answer 3

这个方法有点混乱，但是在过去和 web 上，我见过人们使用 base64 编码的字符串来做到这一点。你可以使用 xml 值将字符串包装起来，然后将其转换为 varbinary。下面是一个示例：

http://blogs.msdn.com/b/sqltips/archive/2008/06/30/converting-from-base64-to-varbinary-and-vice-versa.aspx

我无法亲自证明这种方法的效果或性能，特别是对于大型值而言。因为它充其量只是一个丑陋的 hack，所以我会将它藏在一个 UDF 中，这样如果找到更好的方法，你可以轻松地更新它。

- Joel Coehoorn

Answer 4

我以前从未尝试过这样的事情，但从SQL Server 2008 R2的文档中得知，使用SUBSTRING应该可以获取整个varbinary值，尽管您可能需要分块处理它，使用带有.WRITE子句的UPDATE将数据添加到其中。

更新大型值数据类型

使用.WRITE (expression, @Offset, @Length) 子句对varchar(max)，nvarchar(max)和varbinary(max)数据类型执行部分或完全更新。例如，对于varchar(max)列的部分更新可能仅删除或修改列的前200个字符，而完全更新将删除或修改列中的所有数据。

为获得最佳性能，我们建议以8040字节的倍数为块大小插入或更新数据。

希望这可以帮到您。

- Jeff Ogata

---

你能提供更多的细节吗？虽然到目前为止你提供的所有帮助都非常感谢。 - Issa Fram

@Issa Fram，我不确定你是如何生成你的脚本的，但你可以尝试使用SUBSTRING和DATALENGTH来获取整个varbinary值。如果这样做可以在你的脚本中产生可用的插入语句，那么你就完成了。如果不行，你可以尝试使用循环来获取varbinary数据的块（比如8040字节），并创建更新语句将这些块写入目标行。但希望使用SUBSTRING来获取整个值会起作用。 - Jeff Ogata