How to insert binary data (like images/documents) into a SQL Server database with SQL Server Management Studio?

  • [アーティクル]

If you quickly want to insert some binary data (like images, word documents, pdfs) into a database writing a front-end application for this talk might be a bit of an overkill… Fortunately, this is a pretty straight-forward talk in SQL Server Management Studio :-)

The following example updates the Categories table of the good ol’ Northwind database to store the images, updates two categories with images and adds another category and an image.

 /* Add anadditional column to the Categories table to store the image */

ALTER TABLE dbo.Categories ADD       CategoryPicture VARBINARY(MAX) NULL GO

/* update thetable to insert some images */

UPDATE Categories SET CategoryPicture =       (SELECT * FROMOPENROWSET(BULK N'C:\Temp\Beverages.jpg', SINGLE_BLOB) AS CategoryImage) WHERE CategoryID = 1

UPDATE Categories SET CategoryPicture =       (SELECT * FROMOPENROWSET(BULK N'C:\Temp\Condiments.jpg', SINGLE_BLOB) AS CategoryImage) WHERE CategoryID = 2

GO

/* Insert a new category with an image */

INSERT INTO Categories(CategoryName, CategoryPicture) Values ('Another Category', (SELECT * FROM OPENROWSET(BULK N'C:\Temp\AnotherCategory.jpg', SINGLE_BLOB) AS CategoryImage))

Enjoy!

   Daniel

Comments

  • Anonymous
    September 13, 2011
    how to insert image in sqlserver 2005

  • Anonymous
    September 14, 2011
    Subqueries are not allowed in this context. Only scalar expressions are allowed.

  • Anonymous
    September 14, 2011
    Subqueries are not allowed in this context. Only scalar expressions are allowed. i got this error

  • Anonymous
    January 11, 2015
    Excelent, i just need to adda a space here 'FROM OPENROWSET'.  Thanks!

  • Anonymous
    April 24, 2015
    INSERT INTO Categories(CategoryName, CategoryPicture) Values ('Another Category', (SELECT * FROM OPENROWSET(BULK N'C:TempAnotherCategory.jpg', SINGLE_BLOB) AS CategoryImage)) error: Subqueries are not allowed in this context. Only scalar expressions are allowed.

  • Anonymous
    May 20, 2015
    Thanks a lot ; you made my day, I had a hard time find this info online. I really appreciate your work. Great it worked for me.

  • Anonymous
    October 28, 2015
    The comment has been removed

  • Anonymous
    October 28, 2015
    How to insert the resume doc in the table.I have to follow your way,and insert the image.but error will be araised.how could i insert the resume in one table in sql server 2008..please any one help me to insert the resume

  • Anonymous
    January 12, 2016
    insert into tbl_EmpDocumentInfo (docx_ID,emp_Image) SELECT '1', BulkColumn FROM Openrowset( Bulk 'D:malinga.jpg', Single_Blob) as img