Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert binary data into sql server using SSMS

Is there a way to insert binary data into sql server directly from SQL Server management studio?

like image 965
Daniel Avatar asked Dec 15 '09 08:12

Daniel


People also ask

How do you write binary data in SQL?

The syntax for declaring Binary variable is binary(n) , where n defines the size in bytes. Note that size is in bytes and not number of characters. For Example, when we declare as binary(10) , The column will occupy 10 bytes of storage. The value of n can be from 1 to 8000 bytes.

Which datatype is used to store binary in SQL?

Binary data can be stored in a table using the data type bytea or by using the Large Object feature which stores the binary data in a separate table in a special format and refers to that table by storing a value of type oid in your table.


2 Answers

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

like image 95
Daniel Avatar answered Oct 20 '22 06:10

Daniel


Try this:

INSERT INTO Table (field1) VALUES (0xABCDEF) 

Where 0xABCDEF is your binary data represented as an hexadecimal sequence.

like image 27
Rubens Farias Avatar answered Oct 20 '22 06:10

Rubens Farias