Insert file data into varbinary column

Overview

• In SQL Server, OPENROWSET can read from a data file using BULK provider
• SINGLE_BLOB returns the contents of data file as a single-row, single-column rowset of type varbinary(max).

select * from openrowset(bulk 'C:\tmp\happy-new-year.jpg', single_blob) as filedata

Demo

Create new table

create table files (
	id int not null identity(1, 1) primary key, 
	name varchar(100) not null, 
	content varbinary(max) not null
)

Insert file data

insert into files(name, content) 
select 'happy-new-year.jpg', * from openrowset(bulk 'C:\tmp\happy-new-year.jpg', single_blob) as filedata

Select data

select * from files

References:

• https://docs.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16
• https://stackoverflow.com/questions/48886488/insert-file-content-in-varbinary-column