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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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  lt  Server   D  lt  DataBase   T mytable  C mypictureblob  F  C  picture png   W where RecId    I

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to blob