How to convert and store image on SQL Server

Question

I would like to store large images in SQL Server and later use them in Reporting Services, Power BI and Analysis Services.

I found some help, but I still don't understand what is the best way to store them and how to convert these images in the correct format.

Should I convert them to Base64? How do I do that?

I found good explanations on Convert Image DataType To String in SQL Server, Storing images in SQL Server?, but none of them worked with me.

So I have on my database, the path of the image, the image it self and the extension as below:

SELECT
    NM_DIRETORIO AS NM_PATH   ,
    NM_FOTO      AS NM_PICTURE,
    TP_EXTENSAO  AS TP_EXTENSION
FROM D_GB_FOTOS

As I saw it on from this video SSRS - Read images from the SQL Server database, he uses varbinary(max) to store images, but I don't how he converted to that. Also, from Chriss Webb's: Storing Large Images In Power BI Datasets, he uses Base64 to show on Power BI.

So my question is, since I'll use large images, how to I convert a simple image (path + picture) to store in my SQL Server database?

Information:

• SQL Server 2019 (v15.0.18330.0)
• SQL Server Management Objects (SMO) v16.100.37971.0
• Microsoft SQL Server Management Studio v18.5

---

EDIT:

Based on @Peter Schneider answer, I have created a cursor for updating the table, with that value. But I got error on the where clause (e.g: TABLE.ID_COLUMN).

My cursor:

DECLARE @ID_FOTO INT;
DECLARE @CD_ARQUIVO VARCHAR(4000);
DECLARE @CD_ARQUIVO_VARBINARY VARCHAR(4000);
DECLARE @tsql NVARCHAR (4000);

DECLARE CUR CURSOR FOR SELECT ID_FOTO, CD_ARQUIVO, NM_DIRETORIO + '\' + NM_FOTO + TP_EXTENSAO AS CD_ARQUIVO_VARBINARY FROM D_GB_FOTOS WHERE LINORIGEM <> 'CARGA MANUAL'
OPEN CUR

FETCH NEXT FROM CUR INTO @ID_FOTO, @CD_ARQUIVO, @CD_ARQUIVO_VARBINARY

WHILE @CD_ARQUIVO IS NULL BEGIN
    SET @tsql = 'UPDATE D_GB_FOTOS' +
                'SET CD_ARQUIVO = (SELECT CD_ARQUIVO.* from Openrowset(Bulk' + @CD_ARQUIVO + ', Single_Blob) CD_ARQUIVO)' +
                'WHERE ' + @ID_FOTO + ' = D_GB_FOTOS.ID_FOTO;'  
    PRINT (@tsql)
    EXEC  (@tsql)
    FETCH NEXT FROM cur INTO @ID_FOTO, @CD_ARQUIVO
END

CLOSE cur    
DEALLOCATE cur

EDIT 2:

Some adjustments to the query, but there is one final problem where it keeps updating, and doesn't stop with the final ID of the table:

DECLARE @ID_FOTO INT;
DECLARE @CD_ARQUIVO VARCHAR(4000);
DECLARE @CD_ARQUIVO_VARBINARY VARCHAR(4000);
DECLARE @tsql NVARCHAR (4000);
DECLARE @ID_FOTO_MAX INT;

SET @ID_FOTO_MAX = (SELECT MAX(ID_FOTO) AS ID_FOTO FROM D_GB_FOTOS);

DECLARE CUR CURSOR FOR SELECT ID_FOTO, CD_ARQUIVO, (NM_DIRETORIO + '\' + NM_FOTO + TP_EXTENSAO) AS CD_ARQUIVO_VARBINARY FROM D_GB_FOTOS WHERE LINORIGEM <> 'CARGA MANUAL';
OPEN CUR

FETCH NEXT FROM CUR INTO @ID_FOTO, @CD_ARQUIVO, @CD_ARQUIVO_VARBINARY

WHILE (@ID_FOTO <= @ID_FOTO_MAX) BEGIN
    SET @tsql = 'UPDATE D_GB_FOTOS ' +
                'SET CD_ARQUIVO = (SELECT CD_ARQUIVO from Openrowset(Bulk ''' + @CD_ARQUIVO_VARBINARY + ''', Single_Blob) CD_ARQUIVO)' +
                ' WHERE D_GB_FOTOS.ID_FOTO = ' + CONVERT(VARCHAR(10),@ID_FOTO) + ';'  
    PRINT ('ID_FOTO: ' + CONVERT(VARCHAR(10),@ID_FOTO))
    PRINT ('ID_FOTO_MAX: ' + CONVERT(VARCHAR(10),@ID_FOTO_MAX))
    PRINT ('SELECT STATEMENT: ' + @tsql)
    EXEC  (@tsql)
    FETCH NEXT FROM cur INTO @ID_FOTO, @CD_ARQUIVO, @CD_ARQUIVO_VARBINARY
END

CLOSE cur    
DEALLOCATE cur

Example from where I printed the code above:

(1 linha afetada) ID_FOTO: 6529 ID_FOTO_MAX: 6531

(1 linha afetada) ID_FOTO: 6530 ID_FOTO_MAX: 6531

(1 linha afetada) ID_FOTO: 6531 ID_FOTO_MAX: 6531

(1 linha afetada) ID_FOTO: 6531 ID_FOTO_MAX: 6531

(1 linha afetada) ID_FOTO: 6531 ID_FOTO_MAX: 6531

Answer 1

You can use OpenRowSet to read the image from disk and insert it into your table

INSERT INTO YourTableName (ID, VarbinaryMaxColumn) VALUES (1, 
    (SELECT * FROM OPENROWSET(BULK N'C:\Temp\Testimage.png', SINGLE_BLOB) AS VarbinaryMaxColumn)
)