Requirement is to import an image (binary) file to SQL Server with using only the database engine capabilities using simple T-SQL code.

Solution
Using following examples one can save a physical file in database as IMAGE or VARBINARY Data.

This article covers the basic example through which one can save physical file to sql table using “OPENROWSET”, “BULK” and “SINGLE_BLOB”:

  • In SQL Server “OPENROWSET” Bulk Rowset Provider enables to bulk import a data file as a large-object data.
  • varbinary(max) or image, varchar(max) or text, and nvarchar(max) or ntext are the large-object data types supported by OPENROWSET Bulk Rowset Provider

Prerequisite
Before we start examples let’s create a table:
CREATE TABLE [dbo].[M_File](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](64) NULL,
    [File] [image] NULL
)

Example1. Simple insert/import with file name and file path
Insert M_File  ([Name], [File])
Select 'f1.pdf', [FileData].* FROM OPENROWSET (BULK 'C:\Users\TEMP\Desktop\f1.pdf', SINGLE_BLOB)  [FileData]

After insert execute select statement to view the record
select * from M_File

Output. The data is now into the table

Note. File should be exists on the db server otherwise it will give error like “... does not exist or you don't have file access rights.”

Example 2. Dynamic file name and path instead of static
Declare @Name    nvarchar(50)     = 'f1.pdf'
    , @FilePath    nvarchar(100) = 'C:\Users\TEMP\Desktop\'
    , @File        nvarchar(150) = NULL
    , @Query    nvarchar(4000) = NULL

Set @File = @FilePath + @Name

Set @Query =
    'Insert M_File  ([Name], [File])
Select ''' + @Name + ''', [FileData].* FROM OPENROWSET (BULK ''' + @File + ''', SINGLE_BLOB)  [FileData]'

Exec sp_executesql @Query

After insert execute select statement to view the record
select * from M_File

Output. The data is now into the table

Note: The concate (+) operator cannot be used directly with Bulk. Thus, in order to generate a complete insert statement, we must create a dynamic query.
In brief

This is one of the easiest ways to use T-SQL for SQL Server to import an image or file.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.