如何使用TSQL更新SQL SERVER中的BLOB

How do I update a BLOB field only using TSQL (for example from SSMS and not using any code such as ADO.Net or Linq)?

如何仅使用TSQL更新BLOB字段（例如，从SSMS更新而不使用任何代码，如ADO.Net或Linq）？

1 个解决方案

#1

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子句后的相关名，这是必需的。

The second version can be used for a UPDATE as in the following example:

第二个版本可用于UPDATE，如以下示例所示：

UPDATE MyTable 
SET blobField = 
   (SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a) 
WHERE (CriteriaField = @criteria)

For partial updates one can use the SET .WRITE mutator as described in this MSDN article, here is the syntax:

对于部分更新，可以使用本MSDN文章中描述的SET .WRITE mutator，这里是语法：

UPDATE MyTable SET BlobField .WRITE (expression, @offset, @length) WHERE (CriteriaField = @criteria)

Note that the WRITE mutator can only be used on NON-NULL fields.

请注意，WRITE mutator只能用于NON-NULL字段。

In fact this can also be used to do a full update (if the column does not contain NULL), by setting @offset to 0 and @length to NULL (or to the actual length), as in the following example:

实际上，通过将@offset设置为0并将@length设置为NULL（或实际长度），这也可用于执行完全更新（如果列不包含NULL），如下例所示：

DECLARE @tmp VARBINARY(MAX) --Change to the correct datatype here
SELECT @tmp = BulkColumn FROM OPENROWSET (BULK 'C:\Test\Test1.pdf', SINGLE_BLOB) a
UPDATE MyTable SET BlobField .WRITE (@tmp, 0, NULL) WHERE (CriteriaField = @criteria)

#1