Insert or Update a File in Database – SQL Server
Database is a repository for data of various formats. In the past, we use text files to store huge data and technology revolution has changed everything for what we store and How we store data. Big data is a good example. It happened in different forms as punch card, file system, relational database, graph db, NoSQL, NewSQL and etc.
But, in this blog post I am going to show a simple example for storing (insert) and altering (update) a binary image file stored in a table.
Create a Table
CREATE TABLE [dbo].[Images](
ImageID INT IDENTITY(1,1),
ImageDesc VARCHAR(20) NOT NULL,
[ImageData] VARBINARY(MAX) NOT NULL
) ON [PRIMARY]
Sample Image file – SQL2016.jpg
Insert a record with a Image file
SELECT ‘SQL2016′ AS ImageDesc, * FROM OPENROWSET(BULK N’C:\Test\SQL2016.jpg’, SINGLE_BLOB) AS Img
Update an existing record with Image file
SET ImageDesc = ‘SQLServer’
,ImageData = BulkColumn from Openrowset( Bulk ‘C:\Test\SQLServer.jpg’, Single_Blob) as Img
WHERE ImageID = 1
This blog post is for code reference. You can use this code with not restriction.