如何使用 TSQL 在 SQL SERVER 中更新 BLOB [英] How To Update A BLOB In SQL SERVER Using TSQL

问题描述

如何仅使用 TSQL(例如从 SSMS 而不使用 ADO.Net 或 Linq 等任何代码)更新 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 有两种方法:

There are two ways to SELECT a BLOB with TSQL:

SELECT * FROM OPENROWSET (BULK 'C:TestTest1.pdf', SINGLE_BLOB) a

还有:

SELECT BulkColumn FROM OPENROWSET (BULK 'C:TestTest1.pdf', SINGLE_BLOB) a

注意 FROM 子句后的相关名称，这是强制性的.

Note the correlation name after the FROM clause, which is mandatory.

第二个版本可用于更新，如下例所示:

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

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

对于部分更新，可以使用 thisMSDN文章，语法如下:

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

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

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

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

事实上，这也可以用于进行完全更新(如果列不包含 NULL)，通过将 @offset 设置为 0 并将 @length 设置为 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:

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

这篇关于如何使用 TSQL 在 SQL SERVER 中更新 BLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！