Bulk import pictures into NopCommerce 4.2

BulkImportNopPictures.sql

/*
exec sp_configure 'show advanced options', '1'
RECONFIGURE
exec sp_configure 'xp_cmdshell', '1' 
RECONFIGURE
*/
SET NOCOUNT ON;

DECLARE @FilePath nvarchar(max) = 'C:\Temp\images\';
DECLARE @Command nvarchar(255) = 'dir '+@FilePath+' /b', @FileName nvarchar(255), @Sku nvarchar(255), @Index int, @BinaryData VARBINARY(MAX), @InsertedImageId int, @NopCommerceProductId int;
DECLARE @files TABLE ([Id] INT IDENTITY, [FileName] VARCHAR(100))
DECLARE @ProccessedPId TABLE ([Id] INT)
INSERT INTO @files EXECUTE xp_cmdshell @Command

SET @Command =  'MKDIR ' + @FilePath + 'imported'
EXECUTE xp_cmdshell @Command, NO_OUTPUT

DECLARE FileCur CURSOR STATIC FOR
	SELECT F.*, P.Id	
	FROM (SELECT [FileName], SUBSTRING([FileName], 0, CHARINDEX('_', [FileName])) AS [Sku], TRY_CAST(REPLACE(SUBSTRING([FileName], CHARINDEX('_', [FileName]) + 1, 99), '.jpg', '') AS int) AS [Index] FROM @files) F
	JOIN [Product] P ON P.Sku=F.Sku
	WHERE [FileName] LIKE '%.jpg' ORDER BY [FileName]

OPEN FileCur

WHILE 1=1
BEGIN
	FETCH NEXT FROM FileCur INTO @FileName, @Sku, @Index, @NopCommerceProductId
	IF @@FETCH_STATUS <> 0 BREAK;
	PRINT 'Adding image for Sku ' + @Sku + ', ' + CAST(@Index AS nvarchar)

	SET @Command = N'SELECT @BinaryData1 = CAST(bulkcolumn AS varbinary(MAX)) from OPENROWSET(BULK ''' + @FilePath + @FileName + ''', SINGLE_BLOB) ROW_SET'
	EXEC sp_executesql @Command, N'@BinaryData1 VARBINARY(MAX) OUTPUT',@BinaryData1 = @BinaryData OUTPUT
	
	IF NOT EXISTS (SELECT 1 FROM @ProccessedPId WHERE Id = @NopCommerceProductId)
	BEGIN
		DELETE FROM [Picture] WHERE Id IN (SELECT PictureId FROM Product_Picture_Mapping WHERE ProductId = @NopCommerceProductId)
		DELETE FROM [PictureBinary] WHERE PictureId IN (SELECT PictureId FROM Product_Picture_Mapping WHERE ProductId = @NopCommerceProductId)
		DELETE FROM [Product_Picture_Mapping] WHERE ProductId = @NopCommerceProductId

		INSERT INTO @ProccessedPId VALUES (@NopCommerceProductId)
	END

	INSERT INTO [Picture] VALUES ('image/jpeg', NULL, NULL, NULL, 0, NULL);
	SET @InsertedImageId = CAST(scope_identity() AS int);
	INSERT INTO PictureBinary VALUES (@BinaryData, @InsertedImageId);
	INSERT INTO Product_Picture_Mapping VALUES(@NopCommerceProductId, @InsertedImageId, @Index);

	SET @Command = 'MOVE ' + @FilePath + @FileName + ' ' + @FilePath + 'imported\' + @FileName
	EXECUTE xp_cmdshell @Command, NO_OUTPUT
END

DEALLOCATE FileCur

GO