如何为具有VARBINARY(MAX)字段的表生成INSERT脚本? [英] How can I generate an INSERT script for a table with a VARBINARY(MAX) field?

问题描述

我有一个带有VARBINARY(MAX)字段的表(带有FILESTREAM的SQL Server 2008)

I have a table with a VARBINARY(MAX) field (SQL Server 2008 with FILESTREAM)

我的要求是，当我要部署到生产环境时，只能为我的IT团队提供一组按特定顺序执行的SQL脚本.我正在生产中的新表具有此VARBINARY(MAX)字段.通常，对于新表，我将为CREATE TABLE脚本编写脚本.而且，如果我有需要使用的数据，那么我将为INSERT脚本编写脚本.不太复杂.

My requirement is that when I go to deploy to production, I can only supply my IT team with a group of SQL scripts to be executed in a certain order. A new table I am making in production has this VARBINARY(MAX) field. Usually with new tables, I will script out the CREATE TABLE script. And, if I have data I need to go with it, I will then script out the INSERT scripts. Not too complicated.

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

But with VARBINARY(MAX), the Stored Procedure I was using to generate the INSERT statements fails on that table. I tried selecting that field, printing it, copying it, converting to hex, etc. The main issue I have with that is that it doesn't select all the data in the field. I do a check DATALENGTH([FileColumn]) and if the source row contains 1,004,382 bytes, the max I can get the copied or selected data when inserting again is 8000. So basically it is truncated (i.e. invalid) data.....

我该如何做得更好?我像疯了一样尝试了Googling，但是我一定错过了一些东西.记住，我无法访问文件系统.这必须全部用脚本编写.

How can I do this better? I tried Googling this like crazy but I must be missing something. Remember, I can't access the filesystem. This has to be all scripted.

推荐答案

不要从SSMS编写脚本

Don't script from SSMS

bcp 数据输入/输出，或使用类似

bcp the data out/in, or use something like SSMS tools to generate INSERT statements

这篇关于如何为具有VARBINARY(MAX)字段的表生成INSERT脚本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！