如何使用 TSQL 在 SQL SERVER 中更新 BLOB
如何仅使用 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)
相关文章