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 SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



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
-- REVOKE ADMINISTER BULK OPERATIONS TO [login_user]

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







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems