如何使用 sql server management studio 将 blob 插入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1643627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert a blob into a database using sql server management studio
提问by Toad
How can I easily insert a blob into a varbinary(MAX)field?
如何轻松地将 blob 插入varbinary(MAX)字段？
As an example:
举个例子：
thing I want to insert is: c:\picture.png
the table is mytable
the column is mypictureblob
the place is recid=1
我想插入的是： c:\picture.png
表是 mytable
列是 mypictureblob
位置是 recid=1
回答by John Sansom
You can insert into a varbinary(max) field using T-SQL within SQL Server Management Studio and in particular using the OPENROWSET commmand.
您可以在 SQL Server Management Studio 中使用 T-SQL 插入 varbinary(max) 字段，特别是使用 OPENROWSET 命令。
For example:
例如：
INSERT Production.ProductPhoto
(
ThumbnailPhoto,
ThumbnailPhotoFilePath,
LargePhoto,
LargePhotoFilePath
)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET
(BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto
Take a look at the following documentation for a good example/walkthrough
查看以下文档以获得一个很好的示例/演练
Working With Large Value Types
Note that the file path in this case is relative to the targeted SQL server and not your client running this command.
请注意，在这种情况下，文件路径相对于目标 SQL 服务器，而不是运行此命令的客户端。
回答by ZXX
MSDN has an article Working With Large Value Types, which tries to explain how the import parts work, but it can get a bit confusing since it does 2 things simultaneously.
MSDN 有一篇文章Working With Large Value Types，它试图解释导入部分是如何工作的，但它可能会有点混乱，因为它同时做两件事。
Here I am providing a simplified version, broken into 2 parts. Assume the following simple table:
在这里，我提供了一个简化版本，分为 2 个部分。假设有以下简单表：
CREATE TABLE [Thumbnail](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Data] [varbinary](max) NULL
CONSTRAINT [PK_Thumbnail] PRIMARY KEY CLUSTERED
(
[Id] ASC
) ) ON [PRIMARY]
If you run (in SSMS):
如果您运行（在 SSMS 中）：
SELECT * FROM OPENROWSET (BULK 'C:\Test\TestPic1.jpg', SINGLE_BLOB) AS X
it will show, that the result looks like a table with one column named BulkColumn. That's why you can use it in INSERT like:
它将显示，结果看起来像一个名为BulkColumn. 这就是为什么您可以在 INSERT 中使用它，例如：
INSERT [Thumbnail] ( Data )
SELECT * FROM OPENROWSET (BULK 'C:\Test\TestPic1.jpg', SINGLE_BLOB) AS X
The rest is just fitting it into an insert with more columns, which your table may or may not have. If you name the result of that select FOOthen you can use SELECT Foo.BulkColumnand asafter that constants for other fields in your table.
其余的只是将它装入具有更多列的插入物中，您的表可能有也可能没有。如果您命名结果，select FOO那么您可以在表中的其他字段中使用SELECT Foo.BulkColumn和as之后的常量。
The part that can get more tricky is how to export that data back into a file so you can check that it's still OK. If you run it on cmd line:
更棘手的部分是如何将该数据导出回文件，以便您可以检查它是否仍然正常。如果您在 cmd 行上运行它：
bcp "select Data from B2B.dbo.Thumbnail where Id=1"
queryout D:\T\TestImage1_out2.dds -T -L 1
It's going to start whining for 4 additional "params" and will give misleading defaults (which will result in a changed file). You can accept the first one, set the 2nd to 0 and then assept 3rd and 4th, or to be explicit:
它将开始抱怨 4 个额外的“参数”，并将给出误导性的默认值（这将导致文件更改）。您可以接受第一个，将第二个设置为 0，然后使用第三个和第四个，或者明确表示：
Enter the file storage type of field Data [varbinary(max)]:
Enter prefix-length of field Data [8]: 0
Enter length of field Data [0]:
Enter field terminator [none]:
输入字段数据的文件存储类型 [varbinary(max)]:
输入字段数据的前缀长度 [8]: 0
输入字段数据的长度 [0]:
输入字段终止符 [none]:
Then it will ask:
然后它会问：
Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: C:\Test\bcp_2.fmt
您想将此格式信息保存在文件中吗？[是/
否] y主机文件名 [bcp.fmt]: C:\Test\bcp_2.fmt
Next time you have to run it add -f C:\Test\bcp_2.fmtand it will stop whining :-)
Saves a lot of time and grief.
下次你必须运行它时-f C:\Test\bcp_2.fmt，它会停止抱怨:-) 节省大量时间和悲伤。
回答by yoel halb
There are two ways to SELECT a BLOB with TSQL:
有两种使用 TSQL 选择 BLOB 的方法：
SELECT * FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a
As well as:
也：
SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a
Note the correlation name after the FROM clause, which is mandatory.
请注意 FROM 子句后面的相关名称，这是必需的。
You can then this to INSERT by doing an INSERT SELECT.
然后，您可以通过执行 INSERT SELECT 将其插入到 INSERT。
You can also use the second version to do an UPDATE as I described in How To Update A BLOB In SQL SERVER Using TSQL.
您还可以使用第二个版本执行更新，如我在如何使用 TSQL 在 SQL SERVER 中更新 BLOB 中所述。
回答by pbies
However you can simply read a file from disk on SQL server machine:
但是，您可以简单地从 SQL 服务器计算机上的磁盘读取文件：
select * from openrowset (bulk 'c:\path\filename.ext',single_blob) a
to see it in management application in hex form (Management Studio).
以十六进制形式（Management Studio）在管理应用程序中查看它。
So, you can, for example, backup database to file (locally on server) and then download it to other place by the statement above.
因此，您可以例如将数据库备份到文件（在服务器本地），然后通过上述语句将其下载到其他地方。
回答by cagreen
Do you need to do it from mgmt studio? Here's how we do it from cmd line:
你需要从mgmt studio做吗？以下是我们如何从 cmd 行执行此操作：
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TEXTCOPY.exe" /S < Server> /D < DataBase> /T mytable /C mypictureblob /F "C:\picture.png" /W"where RecId=" /I
"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TEXTCOPY.exe" /S <Server> /D <DataBase> /T mytable /C mypictureblob /F "C:\picture.png" /W"where RecId = = /我
回答by Toad
Ok... this took me way too long. The sql-management studio tool is just not up to simple things like this (which I've noticed before when looking for where to set the timeout on queries, and it was done in 4 different locations)
好吧……这花了我太长时间。sql-management studio 工具不能完成这样的简单事情（我之前在寻找在哪里设置查询超时时已经注意到了这一点，它是在 4 个不同的位置完成的）
I downloaded some other sql editor package (sql maestro in my case). And behold it includes a blob editor where you can look at blobs, and load new blobs into these field.
我下载了一些其他的 sql 编辑器包（在我的例子中是 sql maestro）。看哪，它包括一个 blob 编辑器，您可以在其中查看 blob，并将新 blob 加载到这些字段中。
thanks for the input!
感谢您的投入！
