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

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

  • Comments 3

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

Leave a Comment
  • Please add 3 and 7 and type the answer here:
  • Post
  • how to insert image in sqlserver 2005

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

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

Page 1 of 1 (3 items)