How to update BLOB in SQL SERVER using TSQL

Question

How to update the BLOB field only using TSQL (for example, from SSMS and not use any code like ADO.Net or Linq)?

Answer 1

There are two ways to CHOOSE BLOB with TSQL:

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 required.

The second version can be used for 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, you 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) 

Please note that WRITE garbage can only be used for NON-NULL fields.

In fact, this can also be used for 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:\Test\Test1.pdf', SINGLE_BLOB) a UPDATE MyTable SET BlobField .WRITE (@tmp, 0, NULL) WHERE (CriteriaField = @criteria)