How to insert a blob into a database using sql server management studio

Question

How can I easily insert a blob into a varbinary(MAX) field?

As an example:

thing I want to insert is: c:\picture.png
the table is mytable
the column is mypictureblob
the place is recid=1

Answer 1

You can insert into a varbinary(max) field using T-SQL within SQL Server Management Studio and in particular using the OPENROWSET commmand.

For example:

INSERT Production.ProductPhoto 
(
    ThumbnailPhoto, 
    ThumbnailPhotoFilePath, 
    LargePhoto, 
    LargePhotoFilePath
)
SELECT ThumbnailPhoto.*, null, null, N'tricycle_pink.gif'
FROM OPENROWSET 
    (BULK 'c:\images\tricycle.jpg', SINGLE_BLOB) ThumbnailPhoto

Take a look at the following documentation for a good example/walkthrough

Working With Large Value Types

Note that the file path in this case is relative to the targeted SQL server and not your client running this command.

Answer 2

MSDN has an article Working With Large Value Types, which tries to explain how the import parts work, but it can get a bit confusing since it does 2 things simultaneously.

Here I am providing a simplified version, broken into 2 parts. Assume the following simple table:

CREATE TABLE [Thumbnail](
   [Id]        [int] IDENTITY(1,1) NOT NULL,
   [Data]      [varbinary](max) NULL
CONSTRAINT [PK_Thumbnail] PRIMARY KEY CLUSTERED 
(
[Id] ASC
) ) ON [PRIMARY]

If you run (in SSMS):

SELECT * FROM OPENROWSET (BULK 'C:\Test\TestPic1.jpg', SINGLE_BLOB) AS X

it will show, that the result looks like a table with one column named BulkColumn. That's why you can use it in INSERT like:

INSERT [Thumbnail] ( Data )
SELECT * FROM OPENROWSET (BULK 'C:\Test\TestPic1.jpg', SINGLE_BLOB) AS X

The rest is just fitting it into an insert with more columns, which your table may or may not have. If you name the result of that select FOO then you can use SELECT Foo.BulkColumn and as after that constants for other fields in your table.

The part that can get more tricky is how to export that data back into a file so you can check that it's still OK. If you run it on cmd line:

bcp "select Data from B2B.dbo.Thumbnail where Id=1" 
queryout D:\T\TestImage1_out2.dds -T -L 1 

It's going to start whining for 4 additional "params" and will give misleading defaults (which will result in a changed file). You can accept the first one, set the 2nd to 0 and then assept 3rd and 4th, or to be explicit:

Enter the file storage type of field Data [varbinary(max)]:
Enter prefix-length of field Data [8]: 0
Enter length of field Data [0]:
Enter field terminator [none]:

Then it will ask:

Do you want to save this format information in a file? [Y/n] y
Host filename [bcp.fmt]: C:\Test\bcp_2.fmt

Next time you have to run it add -f C:\Test\bcp_2.fmt and it will stop whining :-) Saves a lot of time and grief.

Answer 3

There are two ways to SELECT a 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 mandatory.

You can then this to INSERT by doing an INSERT SELECT.

You can also use the second version to do an UPDATE as I described in How To Update A BLOB In SQL SERVER Using TSQL .

Answer 4

However you can simply read a file from disk on SQL server machine:

select * from openrowset (bulk 'c:\path\filename.ext',single_blob) a

to see it in management application in hex form (Management Studio).

So, you can, for example, backup database to file (locally on server) and then download it to other place by the statement above.

Answer 5

Do you need to do it from mgmt studio? Here's how we do it from cmd line:

"C:\Program Files\Microsoft SQL Server\MSSQL\Binn\TEXTCOPY.exe" /S < Server> /D < DataBase> /T mytable /C mypictureblob /F "C:\picture.png" /W"where RecId=" /I

Answer 6

Ok... this took me way too long. The sql-management studio tool is just not up to simple things like this (which I've noticed before when looking for where to set the timeout on queries, and it was done in 4 different locations)

I downloaded some other sql editor package (sql maestro in my case). And behold it includes a blob editor where you can look at blobs, and load new blobs into these field.

thanks for the input!