SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

Save Image to Database Table in SQL Server

To save image to database table is a requirement for many SQL Server database driven applications. In order to insert image to SQL Server database table, besides using ASP.NET web applications database administrators can use T-SQL commands too. To save image in SQL Server database table in binary format, the easiest method is to execute an SQL OPENROWSET command with BULK and SINGLE_BLOB options.

Let's assume that your SQL Server database administrator creates database table named DatabaseImageTable using following SQL create script.
The table has a column named image to store image files binary data.

CREATE TABLE DatabaseImageTable (
 [image name] nvarchar(100),
 [image] varbinary(max)

After we have created sample storage SQL table to save image into, we are rady to execute following SQL OPENROWSET command.
Below INSERT INTO table command reads the image file from given path on server where SQL Server database instance is running and will save it to varbinary() column.

INSERT INTO DatabaseImageTable ([image name], [image])
SELECT 'SQL Server Image', *
FROM OPENROWSET(BULK N'C:\images\sql-server-image-file.jpg', SINGLE_BLOB) image;

save image in SQL Server database table

If you have enough permissions either server based like execution bulk operations or is a member of the BulkAdmin server role, you should be able to insert and save image file or any other binary file into the target database table. Otherwise, you can experience following SQL Server error message:

Msg 4834, Level 16, State 1, Line 5
You do not have permission to use the bulk load statement.

In order to resolve this issue, you can execute below SQL commands to grant permission to the user for BULK Insert operations on the SQL Server instance.

-- add the bulkadmin role to SQL login
ALTER SERVER ROLE [bulkadmin] ADD MEMBER [login_user]

-- OR
-- grant bulk operations permissions to SQL login
GRANT ADMINISTER BULK OPERATIONS TO [login_user] -- you can take back the granted permissions by running REVOKE command later

SQL Server database developers and administrators can use this simple Insert Into command with OPENROWSET to save image to database table in SQL Server.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.