How to Insert a Blob into a Database Using SQL Server Management Studio

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

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.

insert a BLOB via a sql script?

For testing, you can insert literal hex bytes or use the RAWTOHEX(string) function, as shown below.

create table a(id integer, item blob);
insert into a values(1,'54455354');
insert into a values(2, RAWTOHEX('Test'));
select UTF8TOSTRING(item) from a;
TEST
Test

Addendum: For loading BLOB fields from a file, FILE_READ(fileNameString) may be a useful alternative.

insert into a values(3, FILE_READ('file.dat'));

Can you embed blob data in a script for sql server?

Assuming you are referring to a field of type BINARY / VARBINARY / IMAGE, you should be able to just specify the Hex Bytes such as:

0x0012FD...

For example:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD);

You just need to get that string of hex digits from the file. If you already have such a value in the DB already, then just select that row and field in SSMS, and copy / paste the value from the cell (in "Results to Grid" mode) into your SQL script.

You can also wrap long lines using a backslash as follows:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD\
12B36D98\
D523);

If wrapping via back-slash, be sure to start each new line at the first position as the entire thing is treated as a continuous string. Hence, indenting lines that come immediately following a back-slash would then have spaces between the hex digits, which is not valid. For example:

INSERT INTO TableName (IDField, BlobField) VALUES (1, 0x0012FD\
  12B36D98\
D523);

equates to:

0x0012FD  12B36D98D523

How to insert binary data into sql server using SSMS

Found the answer:

SQL Server has an "OPENROWSET" command that accepts a filepath.

eg

Update myTable
set Image = (
SELECT *
FROM OPENROWSET(BULK N'C:\image.png', SINGLE_BLOB) test)
where ImageID = 1 

Source: http://shortfastcode.blogspot.com/2009/12/insert-binary-data-like-images-into-sql.html

Insert a file PDF in a table SQL server using management studio

Ok, if I understand your question correctly the answer would be something like this:

INSERT INTO nametable
            (batch,
             filereport)
VALUES      ('batch_number1',
             (SELECT *
              FROM   OPENROWSET(BULK 'c:\File1.pdf', single_blob) AS fileReport)
)  

I think your issue is that you're not doing the VALUES clause on your INSERT statement

By the way, credit where it's due; I got that answer from this web page

SQL Server : inserting image

If you have already inserted the details, then you need to update the image column_name:

    UPDATE Tablename
    SET column_name =
    (SELECT * FROM
    OPENROWSET(BULK N'path', SINGLE_BLOB) AS ORS)

Inserting pyodbc.Binary data (BLOB) into SQL Server image column

First make sure you use with open(..) to read the file (another example). This automatically closes file objects when they are exhausted or an exception is raised.

# common vars
connection = pyodbc.connect(...)
filename = 'Test.ics'
insert = 'insert into documents (name, documentType, document, customerNumber)'

# without hex encode
with open(filename, 'rb'):
    bindata = f.read()

# with hex encode
with open(filename, 'rb'):
    hexdata = f.read().encode('hex')

# build parameters
binparams = ('test200.ics', 'text/calendar', pyodbc.Binary(bindata), 1717)
hexparams = ('test200.ics', 'text/calendar', pyodbc.Binary(hexdata), 1717)

# insert binary
connection.cursor().execute(insert, binparams)
connection.commit()

# insert hex
connection.cursor().execute(insert, hexparams)
connection.commit()

# print documents
rows = connection.cursor().execute('select * from documents').fetchall()
for row in rows:
    try:
        # this will decode hex data we inserted
        print str(row.document).decode('hex')
    # attempting to hex decode binary data throws TypeError
    except TypeError:
        print str(row.document)

I'm guessing you are getting the 0x343234353... data by looking at results in Management Studio:

This doesn't mean the data is stored this way, it's just the way Management Studio represents image, text, ntext, varbinary, etc. datatypes in the result pane.

---

Related Topics