How to insert binary data into sql server using SSMS

Question

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

Daniel · Accepted Answer · 2009-12-15 09:24:36Z

31

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

edited Dec 15, 2009 at 9:24

answered Dec 15, 2009 at 9:07

Daniel

2,8041 gold badge33 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ed Harper Over a year ago

This requires that OPENROWSET is enabled on the server - it is disabled by default.

Rubens Farias · Accepted Answer · 2009-12-15 09:08:47Z

28

Try this:

INSERT INTO Table (field1) VALUES (0xABCDEF)

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

answered Dec 15, 2009 at 9:08

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Comments

themefield · Accepted Answer · 2020-07-19 06:30:09Z

1

I found no way to upload/download binary file from SSMS GUI without script.

So I switch to MySQL with Workbench. From their table editor, for a BLOB column, in the context menu, there are Load Value From File... and Save Value To File... options.

answered Jul 19, 2020 at 6:30

themefield

4,43337 silver badges34 bronze badges

Collectives™ on Stack Overflow

How to insert binary data into sql server using SSMS

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related