Post
Ask Question

Simple Image or File Import Using T-SQL for SQL Server

Article Overview

  • Background
  • Solution
  • Prerequisite
  • Example 1. Simple insert/import with file name and file path
  • Example 2. Dynamic file name and path instead of static
  • Summary

Background

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

Data 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

Data into the table

Note: We can’t use concate (+) operator directly with BULK. Hence, we have to create dynamic query for complete insert statement.

Summary

This is one of the simple way to Import a Image or File Using T-SQL for SQL Server.

For more details one can refer the following resources:


Similar Articles