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 (9)

  1. venkatesh says:

    how to insert image in sqlserver 2005

  2. S says:

    Subqueries are not allowed in this context. Only scalar expressions are allowed.

  3. S says:

    Subqueries are not allowed in this context. Only scalar expressions are allowed. i got this error

  4. Pep says:

    Excelent, i just need to adda a space here 'FROM OPENROWSET'.  Thanks!

  5. prakash says:

    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.

  6. Anand says:

    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.

  7. elakiya says:

    but above code give one error.such as,

    Msg 4861, Level 16, State 1, Line 2

    Cannot bulk load because the file "C:Desktopelaks_resume.doc" could not be opened. Operating system error code 3(The system cannot find the path specified.).

  8. Senya says:

    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

  9. Mohsin Afzal says:

    insert into tbl_EmpDocumentInfo (docx_ID,emp_Image)

    SELECT '1', BulkColumn

    FROM Openrowset( Bulk 'D:malinga.jpg', Single_Blob) as img