[HOWTO] Convert Image File to SQL data type varbinary using PowerShell

In one of the projects I’m currently working on, I needed to insert a PNG file into a database column of type varbinary.

I failed with my first attempts, but thanks to some blog posts and some threads on StackOverflow, I was able to come up with the following solution by assembling some of the information I found on the Internet.

# to create a hex-string from a PNG file execute the following commands in PowerShell

# replace C:\PATH\TO\FILE.png with the path to the file you want to convert
[Byte[]]$bytes = Get-Content -Encoding Byte C:\PATH\TO\FILE.png
$hexString = ($bytes | ForEach-Object ToString X2) -join ''
$valueToBeInserted = '0x' + $hexString
$valueToBeInserted | clip

After the execution of the above commands, the value to be inserted into the database column of type varbinary is on the clipboard. To add the value to the database column either directly insert it using Microsoft SQL Server Management Studio or by executing the following insert into statement:

-- replace TABLE_NAME, COLUMN_NAME and PASTE_VALUE_TO_BE_INSERTED_HERE
INSERT INTO [dbo].[TABLE_NAME] ([COLUMN_NAME])
VALUES (PASTE_VALUE_TO_BE_INSERTED_HERE);