Prerequisites

SP - XP_CMDSHELL

Enable the xp_cmdshell procedure:

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

Grant EXEC permission on the xp_cmdshell stored procedure:

GRANT EXECUTE ON xp_cmdshell TO [Domain\GroupOrUser]

Create a proxy account that xp_cmdshell will be run under using sp_xp_cmdshell_proxy_account

EXEC sp_xp_cmdshell_proxy_account 'Domain\ServiceAccount', 'ServiceAcountPassword'

Server Role: BulkAdmin

Expand Security
Expand Logins
Right click on your username or group and choose properties (A dialog window appears)
Choose Server Roles
Select bulkadmin to be able to use bulk commands on your database server.

Functions

Base 64 To String

uFnBase64ToString

IF OBJECT_ID (N'[dbo].[uFnBase64ToString]', N'FN') IS NOT NULL  
    DROP FUNCTION uFnBase64ToString;  
GO

CREATE FUNCTION [dbo].[uFnBase64ToString] ( @Input_Base64_Value VARCHAR(MAX) )
RETURNS VARCHAR(MAX)
AS
BEGIN
    RETURN (
		SELECT
			CAST(
                CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Input_Base64_Value"))', 'VARBINARY(MAX)') 
            AS VARCHAR(MAX)
            ) AS RetVal
    )
END
GO

Tables

Please find below the create table script

CREATE TABLE [dbo].[images]
(
	[ImageID] [bigint] PRIMARY KEY IDENTITY(1,1) NOT NULL,
	[Filename] [varchar](100) NOT NULL,
	[PhotoData] [varbinary](max) NOT NULL,
	[CustomOrder] [int] NULL,
	[IsPrimary] [bit] NOT NULL DEFAULT 1,
	[IsActive] [bit] NOT NULL DEFAULT 1,
	[DateCreated] [datetime] NOT NULL DEFAULT (GETDATE())
);

Views

Please find below the create view script, this view also decodes the image data from Base 64 to a string for use in Power BI

CREATE VIEW [dbo].[vImages] as
SELECT	[ImageID] as [Image ID],
		[Filename] as [Filename],
		CAST(CONCAT('data:image/jpeg;base64,',dbo.uFnBase64ToString([PhotoData])) as varchar(max)) as [Photo Data],
		[IsPrimary] as [Is Primary],
		[CustomOrder] as [Custom Order]
FROM [dbo].[images]
WHERE IsActive=1

Stored Procedures

Please find below the code to create a stored procedure to import images from a folder

CREATE PROCEDURE [dbo].[ImportImages] 
AS
BEGIN
	SET NOCOUNT ON;
	SET TEXTSIZE -1;

 	-- Declarations
	DECLARE @RowNum bigint;
	DECLARE @Counter bigint;
	DECLARE @ImageFolderPath varchar(1000);
	DECLARE @Filename varchar(1000);
	DECLARE @Path2OutFile varchar(2000);
	DECLARE @tsql varchar(2000);

	-- Create a temporary table to list all of the files in the drop folder
	IF OBJECT_ID('tempdb..#ImageList', 'U') IS NOT NULL DROP TABLE #ImageList;
	CREATE TABLE #ImageList (
       ID bigint IDENTITY(1,1) PRIMARY KEY,
	   ImageFileName varchar(4000)
	);

	INSERT INTO #ImageList (ImageFileName)
	EXEC xp_cmdshell 'dir /B "C:\Data\Images"';
	DELETE FROM #ImageList WHERE ImageFileName IS NULL;

	SET @RowNum = (SELECT COUNT(1) as RowNum FROM #ImageList);
	SET @Counter = 1;
	SET @ImageFolderPath = 'C:\Data\Images';

	-- Reset Stored Data Table
	TRUNCATE TABLE dbo.images;

	-- Process Files
	WHILE (@Counter<=@RowNum)
	BEGIN
		SET @Filename = (SELECT ImageFileName FROM #ImageList WHERE [ID]=@Counter);
		SET @Path2OutFile = CONCAT (@ImageFolderPath,'\',@Filename);

		-- Build script and execute   -- Build script and execute
		SET @tsql = 'INSERT INTO dbo.images (Filename,PhotoData) ' +
               'SELECT ' +
					'''' + @Filename + '''' + ', * ' + 
               'FROM OPENROWSET( BULK ' + '''' + @Path2OutFile + '''' + ', SINGLE_BLOB) as IMG'
		EXEC (@tsql);
        SET @Counter = @Counter + 1;
	END

	-- Make inactive invalid filetypes
	UPDATE CI
	SET CI.IsActive=0
	FROM dbo.images CI
	WHERE UPPER(RIGHT(CI.[Filename],3)) NOT IN ('jpg','png')
	;

END