如何使用TSQL更新SQL服务器中的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字段(例如，从ssm中使用，而不使用任何代码，例如ADO)。净或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 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:

对于部分更新，您可以使用SET .WRITE mutator，在这个MSDN的文章中，这里是语法:

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

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

注意，写mutator只能用于非空字段。

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:

实际上，这也可以用来做一个完整的更新(如果列不包含NULL)，通过将@offset设置为0，将@length设置为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