手动将varbinary数据插入SQL Server

We have a SQL Server table for user settings. Originally the settings were domain objects which had been serialized as XML into the table but we recently begun serializing them as binary.

我们有一个用于用户设置的SQL Server表。最初的设置是域对象,它已被序列化为XML到表中,但我们最近开始将它们序列化为二进制。

However, as part of our deployment process we statically pre-populate the table with predefined settings for our users. Originally, this was as simple as copying the XML from a customized database and pasting it into an INSERT statement that was ran after the database was built. However, since we've moved to storing the settings as binary data we can't get this to work.

但是,作为部署过程的一部分,我们使用用户的预定义设置静态预填充表格。最初,这就像从自定义数据库中复制XML并将其粘贴到构建数据库之后运行的INSERT语句一样简单。但是,由于我们已将设置存储为二进制数据,因此我们无法使其工作。

How can we extract binary data from a varbinary column in SQL Server and paste it into a static INSERT script? We only want to use SQL for this, we don't want to use any utilities.

我们如何从SQL Server中的varbinary列中提取二进制数据并将其粘贴到静态INSERT脚本中?我们只想使用SQL,我们不想使用任何实用程序。

Thanks in advance, Jeremy

先谢谢你,杰里米

5 个解决方案

#1

You may find it easier to store a template value in a config table somewhere, then read it into a variable and use that variable to fill your inserts:

您可能会发现将模板值存储在某个配置表中更容易,然后将其读入变量并使用该变量填充您的插入:

DECLARE @v varbinary(1000)
SELECT @v = templatesettings from configtable

INSERT INTO usertable VALUES(name, @v, ....)

#2

From SQL Server 2008 onwards you can use Tasks > Generate Scripts and choose to include data. That gives you INSERT statements for all rows in a table which you can modify as needed.

从SQL Server 2008开始,您可以使用“任务”>“生成脚本”并选择包含数据。这为您提供了表中所有行的INSERT语句,您可以根据需要进行修改。

Here's the steps for SQL 2008. Note that the "Script Data" option in SQL 2008 R2 is called "Types of data to script" instead of "Script Data".

以下是SQL 2008的步骤。请注意,SQL 2008 R2中的“脚本数据”选项称为“脚本数据类型”而不是“脚本数据”。

#3

I presume you're OK with utilities like Query Analyzer/Mangement Studio?

我认为你可以使用像Query Analyzer / Mangement Studio这样的工具吗?

You can just copy and paste the binary value returned by your select statement (make sure that you are returning sufficient data), and prefix it with "0x" in your script.

您只需复制并粘贴select语句返回的二进制值(确保返回足够的数据),并在脚本中以“0x”作为前缀。

#4

If I understand you correctly, you want to generate a static script from your data. If so, consider performing a query on the old data that concatenates strings to form the SQL statements you'll want in the script.

如果我理解正确,您希望从数据中生成静态脚本。如果是这样,请考虑对连接字符串的旧数据执行查询,以形成脚本中您需要的SQL语句。

First, figure out what you want the scripted result to look like. Note that you'll need to think of the values you're inserting as constants. For example:

首先,弄清楚你想要的脚本结果是什么样的。请注意,您需要将要插入的值视为常量。例如:

INSERT INTO NewTable VALUES 'value1', 'value2'

Now, create a query for the old data that just gets the values you'll want to move, like this:

现在,为刚刚获取您想要移动的值的旧数据创建一个查询,如下所示:

SELECT value1, value2
FROM OldTable

Finally, update your query's SELECT statement to produce a single concatenated string in the form of the output you previous defined:

最后,更新查询的SELECT语句,以前面定义的输出形式生成单个连接字符串:

SELECT 'INSERT INTO NewTable VALUES ''' + value1 + ''', ''' + value2 + ''''
FROM OldTable

It's a convoluted way to do business, but it gets the job done. You'll need a close attention to detail. It will allow a small (but confusing) query to quickly output very large numbers of static DML statements.

这是一种复杂的经营方式,但它完成了工作。你需要密切关注细节。它将允许一个小的(但令人困惑的)查询快速输出大量的静态DML语句。

#5

David M's suggestion of using the 0x prefixing works but i had to add an extra 0 at the end of varbinary data that i was trying to insert.

大卫M建议使用0x前缀,但我不得不在我试图插入的varbinary数据的末尾添加一个额外的0。

See the * entry below to see the issue with additional 0 that gets added when converting to varbinary or saving to varbinary column

请参阅下面的*条目,以查看在转换为varbinary或保存到varbinary列时添加的附加0的问题

Insert hex string value to sql server image field is appending extra 0

将十六进制字符串值插入sql server图像字段会追加额外的0

#1