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

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

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