Microsoft Business Intelligence: Insert the image into the SQL Server table using Sql script

Wednesday, 30 June 2021

Insert the image into the SQL Server table using Sql script

In the previous post we saw that how to load the image into the database table.

Uploading the Image into sql server using SSIS

https://bageshkumarbagi-msbi.blogspot.com/2021/03/uploading-image-into-sql-server-using.html

We can insert the image in below way.

Using UI Application: as we know we are storing the image into the varbinary data type. So in the application first of all we are converting the image into the binary and then storing the data into the database.
SSIS package: Read here.

Ad hoc we are getting the requirement to inset the image (photo) in the table. In this demo we will see that how we can update the Image into the data base table.

For Demo we are taking the “AdventureWorks2017” Database.

We are inserting a record in the table “Production.ProductPhoto”.

Before inserting the data into the table we need to below property on the server level.

USE MASTER

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'Ole Automation Procedures', 1;

RECONFIGURE;

-- Add 'bulkadmin' to the correct user

ALTER SERVER ROLE [bulkadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]

Now we are writing the below sql script to upload the photo to the database.

We are inserting to the below photo in the table.

See this item is not available into the table.

Below SQL Script is used to upload this photo into the database.

DECLARE @LargePhotoFileName_Path     NVARCHAR(4000),
        @ThumbnailPhotoFileName_path NVARCHAR(4000),
        @Sql_Query                   NVARCHAR(4000),
        @ThumbnailPhotoFileName      NVARCHAR(100)='Bus_small.jpg',
        @Source_Path                 NVARCHAR(500) ='H:\SSIS1\Photo',
        @LargePhotoFileName          NVARCHAR(500)='Bus.jpg'

SET @LargePhotoFileName_Path = Concat(@Source_Path, '\', @LargePhotoFileName)
SET @ThumbnailPhotoFileName_path =
Concat(@Source_Path, '\', @ThumbnailPhotoFileName)
SET @Sql_Query =
'INSERT INTO Production.ProductPhoto(ThumbNailPhoto,ThumbnailPhotoFileName,LargePhotoFileName,LargePhoto) '
+ 'SELECT '
+ '(Select *  FROM Openrowset( Bulk ' + ''''
+ @ThumbnailPhotoFileName_path + ''''
+ ', Single_Blob) as img),' + ''''
+ @ThumbnailPhotoFileName + '''' + ',' + ''''
+ @LargePhotoFileName + '''' + ', * '
+ '  FROM Openrowset( Bulk ' + ''''
+ @LargePhotoFileName_Path + ''''
+ ', Single_Blob) as img'

EXEC (@Sql_Query)