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;
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.