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