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

Question

Hi,

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

for argument sake:

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

I've been googling for some time and I can't find a simple solution

thanks!

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

Answer 2

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 3

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!

Answer 4

MSDN has an atricle Working With Large Value Types which tries to explain how import works but it can get a bit confusing since it does 2 things together. So here's a simplified version and broken into 2 parts. Assume 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)

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)

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

The poart 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 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 (whihc will result in changed file). You can accept 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.